I want to generate a unique 8 character random alphanumeric string in TSQL, for a mail confirm link. We are currently using a GUID but it is not very user friendly.
It only really has to obey a few rules:
Here are 2 ways of solving it
Method 1: This will generate 8 random characters and store them in varchar @r, in order to prevent all the individual characters from being identical, I added a seed to RAND().
DECLARE @r varchar(8)
SELECT @r = coalesce(@r, '') +CHAR(
CASE WHEN r between 0 and 9 THEN 48
WHEN r between 10 and 35 THEN 55
ELSE 61 END + r)
FROM
master..spt_values
CROSS JOIN
(SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
WHERE type = 'P' AND number < 8
Method 2: This method is using a tally table, each character will never occur more than once. The rows used to generate these characters are randomized by ordering on newid()
DECLARE @r varchar(8)
SELECT @r = coalesce(@r, '') + n
FROM (SELECT top 8
CHAR(number) n FROM
master..spt_values
WHERE type = 'P' AND
(number between ascii(0) and ascii(9)
or number between ascii('A') and ascii('Z')
or number between ascii('a') and ascii('z'))
ORDER BY newid()) a
In order to ensure uniqueness for each varchar(8) 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
SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7)
You could use
CHAR(ROUND(RAND() * 93 + 33, 0)) to generate a random character.
Then look to see if that is one of the characters you may disallow, such as single quotes, perhaps, then build up your string, doing all of this in a loop.
