Generate random and unique alphanumeric code in SQL

The key to generating large number of random and unique alpha numeric code is to generate all of it in one go. Store all the codes in a file or a table and server the requested amount of codes whenever required from there.
The SQL script below will generate 10 digit or rather 10 character alpha numeric codes. You can change the number of cross joins to change the number of digits in the code. For example to generate 8 digit code i will have only eight cross joins of the Chars table and eight concatenation of the Digit column in the select line.

SELECT
C1.Digit + C2.Digit + C3.Digit + C4.Digit + C5.Digit + C6.Digit
+ C7.Digit + C8.Digit + C9.Digit + C10.Digit Num
FROM Chars C1, Chars C2, Chars C3, Chars C4, Chars C5,
Chars C6, Chars C7, Chars C8, Chars C9, Chars C10
WHERE C1.Digit IN('A','B','C','D','E','F','G','H','2','3','4','5','6')
AND ASCII(c2.Digit) = ASCII(c3.Digit) + 6 --6th char after dependency char
AND C3.Digit IN('A','C','E','G','K','M','N','P','Q','R','S') --Dependency char
AND c4.Digit = 'R' --Fixed char
AND C5.Digit IN('B','J','K','L','M','N','P','Q','R','S','7','8','9')
AND ASCII(c6.Digit) = ASCII(c3.Digit) + 2
AND C7.Digit IN('T','U','V','W','X','Y','Z','3','4','5','6')
AND C8.Digit NOT IN('O','I','0','1')
AND ASCII(c9.Digit) = ASCII(c3.Digit) + 5
AND C10.Digit NOT IN('O','I','0','1')
ORDER BY NEWID()

Take note of the WHERE condition. I have defined a simple condition for each character of the code. You can use your own conditions. This solves two purposes, one is that it gives a meaning to the generated codes; also it limits the number of codes that will be generated. The script below generates codes in excess of 20 million. If I need more unique codes; ill just change the fixed character and regenerate. I have used ORDER BY NEWID() to randomize the codes as I am using the script to dump all generated codes to a text file using SSIS. If you want to store the codes in table, you can use an INTO clause and skip the ORDER BY clause. Of course then you will want to use ORDER BY NEWID() when you are fetching the codes from the table.

Below is the script for generating the characters table which you will be needing.

CREATE TABLE Chars(Digit VARCHAR(1))
GO
INSERT INTO Chars(Digit)
SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION
SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION 
SELECT '9' UNION SELECT '0' UNION SELECT 'A' UNION SELECT 'B' UNION 
SELECT 'C' UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F' UNION 
SELECT 'G' UNION SELECT 'H' UNION SELECT 'I' UNION SELECT 'J' UNION
SELECT 'K' UNION SELECT 'L' UNION SELECT 'M' UNION SELECT 'N' UNION 
SELECT 'O' UNION SELECT 'P' UNION SELECT 'Q' UNION SELECT 'R' UNION 
SELECT 'S' UNION SELECT 'T' UNION SELECT 'U' UNION SELECT 'V' UNION 
SELECT 'W' UNION SELECT 'X' UNION SELECT 'Y' UNION SELECT 'Z'
GO
About these ads

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SQL and tagged , , , , , , , , , , , . Bookmark the permalink.

7 Responses to Generate random and unique alphanumeric code in SQL

  1. deepak says:

    Hi,

    This is a great code. I would appropriate if you could show me how it can be used only to generate alpha keys, rather than alpha numeric. I have a requirement of such a thing. It needs to be unique throughout the table please.

    Thanks in advance.
    Deepak

    • Amit Singh says:

      The easiest way is to remove the numeric characters from the Chars table (the second script) and only keep alphabets in it. And of course you will have to modify the where conditions so that they won’t reference numeric characters. Please feel free to ask if you need any help with this.

  2. Romil says:

    hi how to generate on 4 digit alpha numric codes

  3. mohan says:

    Hi Amit,

    I realy appriciate the script above.
    Can you please explain what is the formula by which it says that 20 milion of codes can be generated?
    Is it a probability equation? can you please eleborate on this.

    Thanks
    Mohan Chug

  4. Anyway I’m adding this RSS to my e-mail and can look out for much more of your respective interesting content. Ensure that you update this again very soon..

  5. Carlos says:

    Thank you, I’ve just been searching for info about this topic for ages and yours is the best I have discovered till now. But, what about the conclusion? Are you sure about the source?

  6. Ranjith says:

    thanks it was useful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s