The efficient way to generate 4 or 8 characters long random alphanumeric string in SQL

SELECT LEFT(CONVERT(VARCHAR(36),NEWID()),4)+RIGHT(CONVERT(VARCHAR(36),NEWID()),4)

SELECT RIGHT(REPLACE(CONVERT(VARCHAR(36),NEWID()),’-‘,”),8)

DECLARE @chars NCHAR(36)
SET @chars = N’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’
DECLARE @result NCHAR(5)
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
SELECT @result

 

In order to ensure uniqueness for each varchar you can store the results in a table and compare with result in that table. You can also make the varchar longer and just hope for the best.

Happy Coding 🙂

Advertisements

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