What is the most efficient way to generate 8 character random alphanumeric string in TSQL?

Question

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:

be 8 Characters long
be unique
be generated efficiently

Does `LEFT(NEWID(),8)` not do what you need? (In conjunction with a unique constraint) — Martin Smith, Aug 03 '11 at 23:05
Similar question: [1]: http://stackoverflow.com/questions/2520794/php-generate-an-8-character-hash-from-an-integer — sqlHippo, Aug 03 '11 at 23:32
@MartinSmith I was thinking the same, but those will very often be the same numbers and they are not that randomized — t-clausen.dk, Aug 04 '11 at 05:39

score 36 · Answer 1 · edited Dec 10 '15 at 17:29

36

SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7)

edited Dec 10 '15 at 17:29

Mark Kram

5,672
7
51
70

answered Jul 24 '13 at 10:58

Igor Be

898
7
17

You had an extra parenthesis but I removed it. Thanks for sharing +1 – Mark Kram Dec 10 '15 at 17:32
4

Due to the way that [`SUBSTRING`](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql) works, `0,7` will actually produce **6** not **8** characters. Using `1,8` will give you the required 8 – freefaller Oct 31 '17 at 15:15
1

this will convert 8 hex numbers to a varchar. It will eliminate the rest of the alphabet – t-clausen.dk Nov 21 '17 at 11:40
How do you guarantee the uniqueness ? – serefbilge Jan 11 '23 at 22:33

score 20 · Accepted Answer · answered Aug 04 '11 at 07:57

20

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

answered Aug 04 '11 at 07:57

t-clausen.dk

43,517
12
59
92

1

If you're using method 1 for generating large amounts of data, be careful. The line `RAND(ABS(CHECKSUM(NEWID()))` will fall over once every 4 billion times it's called. The issue is that when CHECKSUM produces the largest possible negative int (-2147483648) and that value is fed into the ABS function. The largest possible positive int is 2147483647. This results in an arithmetic overflow. https://learn.microsoft.com/en-us/sql/t-sql/functions/abs-transact-sql?view=sql-server-2017 – Giles Roberts Jul 02 '18 at 12:35
My solution was this expression `RAND(Utilities.CapForRand(ABS(CAST(BINARY_CHECKSUM(NEWID()) as bigint)))` Casting the result of the BINARY_CHECKSUM to a bigint gets around the arithmetic overflow in ABS as the bigint will hold 2147483648 and ABS goes into bigint mode when passed a bigint. However RAND requires an int so I wrote a utility function that takes a bigint, if it's larger than the largest possible positive int then it returns the largest possible positive int, otherwise it returns the passed in value as an int. – Giles Roberts Jul 02 '18 at 12:42
@GilesRoberts, would you mind reviewing http://sqlfiddle.com/#!18/0d24a/2 and tell me if it's right or not. Cheers! – 007 Jun 27 '19 at 14:42

score 0 · Answer 3 · answered Aug 04 '11 at 00:13

0

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.

answered Aug 04 '11 at 00:13

James Black

41,583
10
86
166

1

You are almost sure of getting characters that are not alphanumeric with that script, if you use it in my first example you get 8 identical characters. – t-clausen.dk Aug 04 '11 at 08:09

What is the most efficient way to generate 8 character random alphanumeric string in TSQL?

3 Answers3

Linked