Wednesday, December 24, 2014

How to use MS SQL to generate random unique number for scratchcard

Do you need a series random unique numbers for scratchcards, or bundle codes for vouchers?


There are many ways to do this work, in this articles I would like to share a way to generate these numbers in SQL server.

SQL server has a function calling NEWID(), it creates a unique GUID (uniqueidentifier) value. Depending on your purpose, you can use this function in several ways to generate unique numbers.

Generate a GUID:

SELECT NEWID()

It will generate a new random uniqueidentifier e.g. 4FEC77B5-83AC-485B-9B0A-9A3E58BE4A06

Generate a random unique number:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint))

It will generate a random unique number e.g. 685690842599

Generate a random unique number with fixed digit:

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(6)) AS bigint),15),0,0)

You will have a random unique number with fixed digit e.g. 188125385042097. Then you can use it for your scratchcard or voucher etc.

That's all. Any comment is welcome!

Subscribe to RSS Feed Follow me on Twitter!