SQL SERVER – Simple Method to Generate Fixed Digit OTP Code Using Random Function

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.

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 

Reference: Pinal Dave (https://blog.SQLAuthority.com)