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 OTPNow as RAND() sometimes give 0, to handle it, we can change the code to
SELECT LEFT(CAST(RAND()*1000000000+999999 AS INT),6) as OTPso 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.
SQL SERVER – Random Number Generator Script – SQL Query
SQL SERVER – Using RAND() in User Defined Functions (UDF)
SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
1 Comment. Leave new
Hi Pinal,
Can we use below query also? I know, it’s including Characters in the result.
select LEFT(NEWID(),6)