One of my blog readers sent me the following mail after reading my earlier blog posts about Random Function and OTP Code. Before you read the email, you can read those blog posts here, here, and here.
“I want to generate a six digit number to send the customers who register for the app. It is like an OTP (One Time Password). What is the simpler way to do this in SQL?”
Well. There are several methods to generate a random number. One simple method is to make use of RAND() function available in SQL Server.
RAND() function simply generate a decimal number between 0 (inclusive) and 1 (exclusive). The logic is to multiply value returned by RAND() by 1 billion so that it has enough digits and apply LEFT function to extract 6 digits needed.
SELECT LEFT(CAST(RAND()*1000000000 AS INT),6) as OTP
Now as RAND() sometimes give 0, to handle it, we can change the code to
SELECT LEFT(CAST(RAND()*1000000000+999999 AS INT),6) as OTP
so that you always get 6 digit number.
This is really a simple method of generating 6 digit number. It can be easily extended to any number of digits.
You may be interested to read these posts related to RAND function.
Reference: Pinal Dave (https://blog.SQLAuthority.com)