Generate Fixed Digit OTP Code 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.

1	SELECTLEFT(CAST(RAND()*1000000000 ASINT),6) asOTP

Now as RAND() sometimes give 0, to handle it, we can change the code to

1	SELECTLEFT(CAST(RAND()*1000000000+999999 ASINT),6) asOTP

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.

Source: https://blog.sqlauthority.com/2018/08/22/sql-server-simple-method-to-generate-fixed-digit-otp-code-using-random-function/