MSSQL Random Number
MSSQL has a function CHAR () that converts int (0-255) ASCII code into characters. Then we can use the following ms SQL statement to randomly generate lower-case, upper-case letters, special characters and numbers.
Uppercase letters:
CHAR (ROUND (RAND () * 25 + 65,0 ))
Lowercase letters:
CHAR (ROUND (RAND () * 25 + 97,0 ))
Special characters:
CHAR (ROUND (RAND () * 13 + 33,0 ))
Number:
CHAR (ROUND (RAND () * 9 + 48, 0 ))
A netizen asked the above questions on SKYPE just now.
Okay, Insus. NET is also trying to write a stored procedure to apply the preceding SQL statement. You can refer to the following stored procedure. If you still have questions, continue with the discussion.
Copy codeThe Code is as follows:
Usp_RandomNumber
Create procedure [dbo]. [usp_RandomNumber]
(
@ Len INT = 1, -- random number of digits
@ Rows INT = 1 -- random pen count
)
AS
BEGIN
DECLARE @ t as table ([Random Number] VARCHAR (MAX ))
DECLARE @ l int = 1, @ r int = 1
WHILE @ R <= @ Rows
BEGIN
DECLARE @ RN varchar (MAX) =''
WHILE @ L <= @ Len -- randomly generate the number of bits per number
BEGIN
SET @ RN = @ RN + CHAR (ROUND (RAND () * 9 + 48, 0 ))
SET @ L = @ L + 1
END
-- If the same random number is generated, it will not be stored
If not exists (SELECT [Random Number] FROM @ t where [Random Number] = @ RN)
BEGIN
Insert into @ t select @ RN -- insert into @ T ([Random Number]) VALUES (@ RN)
SET @ R = @ R + 1 -- records a total of several random numbers.
SET @ L = 1 -- after a random number is generated, the number of digits of the random number is initialized to 1.
END
END
SELECT [Random Number] FROM @ T
END
After you attach the stored procedure to the database, you can execute the stored procedure:
Copy codeThe Code is as follows:
EXECUTE [dbo]. [usp_RandomNumber] 8, 10
Result (the result obtained by execute is different each time because it is generated randomly)