Non-repeated SQL Server Random Number

Source: Internet
Author: User
MSSQL has a function CHAR () that converts int (0-255) ASCII code into characters. Then we can use the following MSSQL statement to randomly generate lower-case, upper-case letters, special characters and numbers

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

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.
The 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:
The 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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.