Generate 6 digit alpha numeric value

userODNl1 0
Viewed 45

I have the following code; the only issue with it is that sometimes it generates a digit alphanumeric code.

I am trying to modify it such that it always returns a 6 digit code. Any thoughts on how to tweak this?

I have another procedure the purges duplicates and it re-runs, so that is not an issue.

SET @HashCode = NULL

SELECT    
    @HashCode = ISNULL(@HashCode, '') + SUBSTRING('23456789ABCDEFGHJKLMNPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 36) + 1, 1)
FROM    
    [master]..[spt_values] AS [spt_values] WITH (NOLOCK)
WHERE    
    [spt_values].[type] = 'P'
    AND [spt_values].[number] < 6
tsql stored-procedures sql-server
1 Answers

A very simple way of achieving this is selecting the first 6 LEFT characters in the string generated by the NEWID() function:

SELECT LEFT(NEWID(), 6)
Examples:
4DF32A
DC70D5
8793B2
3D8416
EE2838

Note, because the NEWID() function generates a unique identifier (GUID), consisting of hexadecimal characters (A-F & 0-9), there is a chance the output will consist of only numbers or only letters; which may or may not fit your purpose if you require a strict alphanumeric value.

Examples:
946983
831814
DDFDBB

You can use it like this:

SET @HashCode = NULL

SELECT    
    @HashCode = LEFT(NEWID(), 6)
GRIV0
Related