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 :)

Ebook Download
View all
Learn
View all