How to Generate random alphanumeric sequence

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Generate random alphanumeric sequence

Author

Topic

karthik425
Starting Member

3 Posts

Posted - 2011-08-08 : 10:35:27

How to Generate random 10-digit alphanumeric sequence in sql server,
for example
declare @OrderNumber nvarchar(1000)
set @OrderNumber = 'A1Q2D5J8D3'

it Should be generate random automatically

Thanks & Regards
karthik

karthik

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-08 : 10:46:31

Here's one way
select left(stuff(convert(varchar(36),newid()),9,1,''),10)

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-08 : 10:57:54

Here's another one:

;WITH g(g) AS (SELECT CAST(NEWID() AS BINARY(10))),
n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<10),
a(a) AS (SELECT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
SELECT (SELECT ''+SUBSTRING(a,CAST(SUBSTRING(g,n,1) AS TINYINT)%36+1,1) 
FROM g CROSS JOIN n CROSS JOIN a ORDER BY NEWID() FOR XML PATH(''))

You can further randomize and control the characters by changing the string in the A CTE.

edit: fixed a dupe character

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-08 : 11:03:40

That's way too much thinking for me!

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-08 : 11:04:36

I said the same thing until I tried it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-08 : 15:00:18

Here's a much shorter version that appears to be just as random:

;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<36)
SELECT LEFT((SELECT ''+SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',n,1)
FROM n ORDER BY NEWID() FOR XML PATH('')),10)

karthik425
Starting Member

3 Posts

Posted - 2011-08-09 : 03:09:10

Thank u very much jimf for your suggestion

...you saved my time..

karthik

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-09 : 10:52:57

This may be interesting too
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/20/random-password-generator.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources