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!

No comments:

Post a Comment

Subscribe to RSS Feed Follow me on Twitter!