generate random string with 2 digits and 3 chars

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

generate random string with 2 digits and 3 chars

Author

Topic

girishhande
Starting Member

26 Posts

Posted - 2009-11-06 : 06:52:32

i want to genearate random strings with 2 digits and 3 chars

gkh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 07:41:14

Change this as you want
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/20/random-password-generator.aspx

Madhivanan

Failing to plan is Planning to fail

nalnait
Starting Member

14 Posts

Posted - 2009-11-06 : 09:38:42

use function

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 10:20:58

For what purpose?

1. To generate Sample Data

Or (and here's the real answer...)

2. To Generate Unique Primary Keys in a live Production Database

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-06 : 10:48:50

Or get all 1,757,600 permutations at once?

-- 1,757,600 permutations (10x10x26x26x26)
SELECT		CHAR(n1.Number) + CHAR(n2.Number) + CHAR(a1.Number) + CHAR(a2.Number) + CHAR(a3.Number) AS SecretCode
FROM		master..spt_values AS n1
INNER JOIN	master..spt_values AS n2 ON n2.Type = 'P'
			AND n2.Number BETWEEN 48 AND 57
INNER JOIN	master..spt_values AS a1 ON a1.Type = 'P'
			AND a1.Number BETWEEN 65 AND 90
INNER JOIN	master..spt_values AS a2 ON a2.Type = 'P'
			AND a2.Number BETWEEN 65 AND 90
INNER JOIN	master..spt_values AS a3 ON a3.Type = 'P'
			AND a3.Number BETWEEN 65 AND 90
WHERE		n1.Type = 'P'
		AND n1.Number BETWEEN 48 AND 57
ORDER BY	NEWID()

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 14:42:57

quote:

Originally posted by Peso

Or get all 1,757,600 permutations at once?

-- 1,757,600 permutations (10x10x26x26x26)
SELECT		CHAR(n1.Number) + CHAR(n2.Number) + CHAR(a1.Number) + CHAR(a2.Number) + CHAR(a3.Number) AS SecretCode
FROM		master..spt_values AS n1
INNER JOIN	master..spt_values AS n2 ON n2.Type = 'P'
			AND n2.Number BETWEEN 48 AND 57
INNER JOIN	master..spt_values AS a1 ON a1.Type = 'P'
			AND a1.Number BETWEEN 65 AND 90
INNER JOIN	master..spt_values AS a2 ON a2.Type = 'P'
			AND a2.Number BETWEEN 65 AND 90
INNER JOIN	master..spt_values AS a3 ON a3.Type = 'P'
			AND a3.Number BETWEEN 65 AND 90
WHERE		n1.Type = 'P'
		AND n1.Number BETWEEN 48 AND 57
ORDER BY	NEWID()

N 56°04'39.26"
E 12°55'05.63"

If you weren't getting married, popping kids out left and right, playing in a rock band (with real rocks)...

I'd say you need to get a life

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 14:47:57

must be a SQL 2k8 thing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

girishhande
Starting Member

26 Posts

Posted - 2009-11-08 : 23:54:26

hi guys but i want to generate string with random possitions of numbers and chars not always first two numbers shld be digits

gkh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 01:21:02

quote:
Originally posted by girishhande

hi guys but i want to generate string with random possitions of numbers and chars not always first two numbers shld be digits

gkh

Ok. What is your next constraint?

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 10:09:01

...requirement...what is your next requirement

Why don't you tell us what you are doing and give us the bigger picture

Instead of playing hide and go seek with the requirements

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-09 : 14:02:39

quote:
Originally posted by X002548

...requirement...what is your next requirement

Why don't you tell us what you are doing and give us the bigger picture

Instead of playing hide and go seek with the requirements

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Pffttt. Clear requirements? Where is the challenge in that?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 01:56:10

quote:
Originally posted by DonAtWork

quote:
Originally posted by X002548

...requirement...what is your next requirement

Why don't you tell us what you are doing and give us the bigger picture

Instead of playing hide and go seek with the requirements

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Pffttt. Clear requirements? Where is the challenge in that?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

The real challenge is to guess what OP is going to ask next

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources