Sqlsever t-SQL determines the number of repeated occurrences of input numbers

Use a stored procedure

 

The code is as follows:

Copy code

Alter proc NumShowTimes ( @ NumValue NVARCHAR (MAX) ) AS BEGIN DECLARE @ TempTable TABLE (num NVARCHAR (4) --- define the TABLE for storing numbers DECLARE @ TempNum NVARCHAR (4 )-- DECLARE @ Value nvarchar (max) SET @ Value = @ numValue --- Determine whether the input is a number WHILE (LEN (@ numValue)> 0) BEGIN SET @ TempNum = SUBSTRING (@ numValue, LEN (@ numValue), 1) SET @ numValue = SUBSTRING (@ numValue, 1, (LEN (@ numValue)-1 )) IF @ TempNum LIKE '[^ 0-9]' BEGIN PRINT 'You input not num' RETURN END END ---- Getting data into the table WHILE (LEN (@ Value)> 0) BEGIN SET @ TempNum = SUBSTRING (@ Value, LEN (@ Value), 1) SET @ Value = SUBSTRING (@ Value, 1, (LEN (@ Value)-1 )) Insert into @ TempTable VALUES (@ TempNum) END SELECT num, count (num) AS [count] FROM @ TempTable group by num END

 

Remove the while loop that determines whether the input is a number, and display the number of occurrences of the entered characters, including Chinese characters.

SQL uses the Case When Then Else End multi-condition to judge repeated characters with a specified number of times (generate the maximum sequential number of the day)

 

 

The code is as follows:	Copy code
Select Case When a is not null then When B is not null then B When c is not null then c When d is not null then d Else'' End column name From Table Name

SQL uses Case When Then multi-condition judgment

The code is as follows:

Copy code

Select top 100 State, JoinState, (Case When State = 1 and Joinstate = 0 then 2 When State = 1 and JoinState = 1 then 1 Else 0 End) As usestate From UserInfo (Generate the maximum sequential number of the day) Select substring (convert (varchar (8), getdate (), 112), 1, 8) + '123' + Replicate ('0', 4-len (max_ContractNo) -- repeat character expressions by specified number of times + Cast (max_ContractNo as varchar (4 )) From (select Case When substring (max (My_ContractNo), 12, 4) is null then '1' Else cast (substring (max (My_ContractNo), 12, 4) as bigint) + 1 as varchar (8 )) End max_ContractNo From UserContractNo WHERE datediff (Day, Adddate, getdate () = 0 ) S Create table [UserContractNo] ( [Id] [int] IDENTITY (1, 1) not null, [ApplyUserDetailID] [int] not null, [My_ContractNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS not null constraint [DF_UserContractNo_My_ContractNo] DEFAULT (''), [AddDate] [datetime] not null constraint [DF_UserContractNo_AddDate] DEFAULT (getdate ()), [Remarks] [varchar] (50) COLLATE Chinese_PRC_CI_AS not null constraint [DF_UserContractNo_Remarks] DEFAULT (''), CONSTRAINT [PK_UserContractNo] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY] ) ON [PRIMARY]