MSSQL Random Number
MSSQL has a function of char () is to convert int (0-255) ASCII code to characters. Then we can use the following MS SQL statement to randomly generate lowercase, uppercase, special characters and numbers.
Capital:
CHAR (ROUND (RAND () * 25 + 65,0))
Lowercase letters:
CHAR (ROUND (RAND () * 25 + 97,0))
Special characters:
CHAR (ROUND (RAND () * 13 + 33,0))
Digital:
CHAR (ROUND (RAND () * 9 + 48,0))
just now a netizen in Skype asked the above question.
OK, Insus.net is also trying to write a stored procedure to apply the above SQL statement, you can refer to the following stored procedures, if you have questions please continue to discuss.
Copy Code code as follows:
Usp_randomnumber
CREATE PROCEDURE [dbo]. [Usp_randomnumber]
(
@Len INT = 1,--number of random digits
@Rows INT = 1--Number of random pens
)
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--random generation of each number of digits
BEGIN
SET @RN = @RN + CHAR (ROUND (RAND () * 9 + 48,0))
SET @l = @l + 1
End
--if you produce the same random number, you will not store
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 generate a total of several random numbers
Set @l = 1--initializes the number of random digits to 1 after each generation of a random number
End
End
SELECT [Random number] from @T
End
After you attach the above stored procedure to the database, you can execute the stored procedure:
Copy Code code as follows:
EXECUTE [dbo]. [Usp_randomnumber] 8,10
The resulting result (because it is randomly generated, the results of each execute will be different)
