Create 5000 unique alphanumeric character

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

General SQL Server Forums

New to SQL Server Programming

Create 5000 unique alphanumeric character

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-02-04 : 08:27:07

Hi Guys,

How can I create 5000 unique alphanumeric character in 3 digits in sql server? Any one help me ASAP plz.

Thank you!
Pushkar

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-04 : 08:56:13

Probably not the simplest or most efficient, but here is one way - I limited to alphas, but should be possible to extend to alphanumeric:

SELECT  CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
		CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
		CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)

EDIT: If you want to generate 5,000 of these, use a numbers table, or if you don't have one, generate one on the fly like this:

;WITH N(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n+1 FROM N WHERE n < 5000
)
SELECT  CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
		CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
		CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)
FROM
	N
OPTION (MAXRECURSION 5000);

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-02-05 : 01:18:08

quote:
Originally posted by sunitabeck

Probably not the simplest or most efficient, but here is one way - I limited to alphas, but should be possible to extend to alphanumeric:
SELECT CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)

EDIT: If you want to generate 5,000 of these, use a numbers table, or if you don't have one, generate one on the fly like this:
;WITH N(n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM N WHERE n < 5000
)
SELECT CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)+
CHAR(ASCII('A')+ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%26)
FROM
N
OPTION (MAXRECURSION 5000);

Wonderfull sunita... thanks for the reply... u r always a life saver for me... thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-05 : 07:26:44

You are quite welcome.

I want to point out though, that the values generated may not (and very likely would not be) unique because the way I have done it, there are only 26x26x26 possible combinations, and picking 5,000 out of those randomly will produce dups with a high degree of probability.

If you don't want them to be random, you can always start with AAA, AAB etc.and go down the line until you hit 5,000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources