How to generate random numbers in sql?

Question

How to generate unique and random numbers (5 characters) in sql command?

Sample data:

Column K_ID and many row 
ID1:63487 
ID2:21583

Squirrel · Accepted Answer · 2018-08-07T12:37:09.253

2

use NEWID()

select  10000 + abs(convert(bigint, convert(varbinary(20), newid()))) % 90000

edited Aug 07 '18 at 12:37

answered Aug 07 '18 at 12:30

Squirrel

OP - This is an excellent way to generate a number with a length of 5 but you still have to check and call this again (and again, and again) if it returns a value you already have. And with a length of 5 numbers you are going to run out pretty quickly. – Sean Lange Aug 07 '18 at 13:25

score 0 · Answer 2 · answered Aug 07 '18 at 12:27

0

SELECT RIGHT('0000' + CAST(CAST(FLOOR(RAND() * 10000) as int) as varchar(5)), 5)

answered Aug 07 '18 at 12:27

Cato

Just a minor detail: should be * 100000. At the moment it results in 01441 for example. – SQL_M Aug 07 '18 at 13:48
If you try to do this in a query the Rand() function will calculate a value once, then re-use that same value on every line in the return. – orgtigger Aug 07 '18 at 19:57

2 Answers2