Generating Random String - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Generating Random String

Author

Topic

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2006-09-12 : 20:38:26

Evening Folks!

I'm trying to create what will eventually be a UDF to generate a random 8-character string of mixed case, alpha-numeric characters.

Here's what I've got so far:

declare @pool varchar(100)
declare @counter int
declare @pos int
declare @rstring varchar(8)

set @pool = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
set @counter = 1

while @counter <= 8
begin
set @counter = @counter + 1
set @pos = ceiling(rand()*(len(@pool)))
set @rstring = @rstring + substring(@pool, @pos, 1)
end

select @rstring

Essentially, I'm trying to concatenate 8 characters together to make up this random string. Unfortunately, all I'm getting back is a NULL value.

I've been staring at this for an hour or so and could use a 2nd set of eyes to tell me what I've missed.

Thanks,

Bob

darinh
Yak Posting Veteran

58 Posts

Posted - 2006-09-12 : 21:05:14

It is because @rstring is a null to start with and then you are trying to add to the null value

set @rstring = '' after your declare

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2006-09-13 : 00:34:35

Hi Darinh!
You know, I tried that too and ended up with a similar result. Only instead of null, it was '', the same as I started with.

Thanks,

Bob

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 00:37:13

quote:
Originally posted by oitsubob

Hi Darinh!
You know, I tried that too and ended up with a similar result. Only instead of null, it was '', the same as I started with.

Thanks,

Bob

where did you place the set @rstring = '' statement ? Place it before while loop

or change to

 set @rstring = isnull(@rstring, '') + substring(@pool, @pos, 1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 00:39:56

Actually why don't you make use of the codes from this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59194

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2006-09-13 : 02:05:15

quote:
Originally posted by khtan

quote:
Originally posted by oitsubob

Hi Darinh!
You know, I tried that too and ended up with a similar result. Only instead of null, it was '', the same as I started with.

Thanks,

Bob

where did you place the set @rstring = '' statement ? Place it before while loop

or change to
set @rstring = isnull(@rstring, '') + substring(@pool, @pos, 1)

KH

KH,
I did have it before the loop, but I'll try your other method as well.

Thanks,

Bob

rme8494
Yak Posting Veteran

98 Posts

Posted - 2006-09-13 : 09:04:39

Bob,
This doesn't quite fit your needs 100% but this is what I use to create a password that is lower case mix of alphas and numerics.

SELECT @vNewPass = LOWER(LEFT(NEWID(),8))

Pretty darn simple.

If you did want a mix case you could do something like this I suppose

SELECT @vNewPass = LOWER(LEFT(NEWID(),4)) + LEFT(NewID(),4)

However that code above would always put the upper case somewhere in your last 4 and lower case in your first four. But still it's quick and easy.

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-13 : 10:28:26

quote:
Originally posted by rme8494

Bob,
This doesn't quite fit your needs 100% but this is what I use to create a password that is lower case mix of alphas and numerics.

SELECT @vNewPass = LOWER(LEFT(NEWID(),8))

Pretty darn simple.

If you did want a mix case you could do something like this I suppose

SELECT @vNewPass = LOWER(LEFT(NEWID(),4)) + LEFT(NewID(),4)

However that code above would always put the upper case somewhere in your last 4 and lower case in your first four. But still it's quick and easy.

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

That will generate a much weaker password, since it can use only 16 characters. There are only 4,294,967,296 possible 8 character passwords.

With upper case, lower case, and numbers, there are 218,340,105,584,896 possible 8 character passwords.

select power(16.,8.)
select power(26.+26.+10.,8.)

CODO ERGO SUM

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2006-09-13 : 12:11:46

Just got into the office and messed with the code some more. I plugged in the @rstring = '' before the loop as I had done yesterday, only now it works -- very strange! Both my local machine and the server were restarted overnight. I wouldn't think that would have made a difference, but it's amazing sometimes what a reboot will cure.

Thanks everyone for taking the time to look at this with me!

Bob

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2006-09-13 : 14:17:58

As a follow-up, after converting my code to a function, I learned you can't use rand() inside a function. I tell ya, I learn something new everyday :)

As a work around, you can create a view, such as:

CREATE VIEW vw_Random
AS
SELECT rand() as Random

I also added the ability to pass in the length of string to the function, but set some minimum requirements that the string be at least 8 characters and no more than 15 in length.

So, here's what I ended up with:

ALTER FUNCTION fn_RandomString(@length tinyint = 8)
RETURNS varchar(255)
AS
BEGIN
-- Strings to be at least 8 characters and no more than 15 in length
SET @length = CASE
WHEN @length < 8 THEN 8
WHEN @length > 15 THEN 15
ELSE @length
END

DECLARE @pool varchar(100)
DECLARE @counter int
DECLARE @rand float
DECLARE @pos int
DECLARE @rstring varchar(15)

SET @pool = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
SET @counter = 1
SET @rstring = ''

WHILE @counter <= @length
BEGIN
SET @counter = @counter + 1
SET @rand = (SELECT random from vw_random)
SET @pos = ceiling(@rand *(len(@pool)))
SET @rstring = @rstring + substring(@pool, @pos, 1)
END

RETURN (@rstring)
END

Thanks again everyone!

Bob

fluffnfur
Starting Member

1 Post

Posted - 2006-09-19 : 10:40:02

Hi Guys

I used this - hope it helps!

to call it simply use (example returns six char password):

select dbo.fGeneratePassword(6,newid())

create function fGeneratePassword(@length int,@newid uniqueidentifier)
returns varchar(255)
as
begin
declare @retval varchar(255)
set @retval = upper(LEFT(@newid,@length))
return @retval
end
go

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 10:57:24

quote:
Originally posted by fluffnfur

Hi Guys

I used this - hope it helps!

to call it simply use (example returns six char password):

select dbo.fGeneratePassword(6,newid())

create function fGeneratePassword(@length int,@newid uniqueidentifier)
returns varchar(255)
as
begin
declare @retval varchar(255)
set @retval = upper(LEFT(@newid,@length))
return @retval
end
go

That would generate a very weak password with only 16,777,216 possible combinations.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 11:15:07

Here is a set based method that can be used to generate as many random 8 character passwords as you want. The code shown will return 100 passwords.


declare @str varchar(200)
declare @mod int

select	@str = 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select	@mod   = len(@str)
select @mod

select
	password =
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R2%@mod)+1,1)+
	substring(@str,(R3%@mod)+1,1)+
	substring(@str,(R4%@mod)+1,1)+
	substring(@str,(R5%@mod)+1,1)+
	substring(@str,(R6%@mod)+1,1)+
	substring(@str,(R7%@mod)+1,1)+
	substring(@str,(R8%@mod)+1,1)
from
(
select
	NUMBER,
	R1 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R2 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R3 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R4 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R5 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R6 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R7 = abs(convert(bigint,convert(varbinary(100),newid()))),
	R8 = abs(convert(bigint,convert(varbinary(100),newid())))
FROM
	-- Function available in Script Library Forum
	F_TABLE_NUMBER_RANGE(1,100) aaa
) aa

CODO ERGO SUM

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-09-19 : 11:48:57

Michael, why does your aa subquery have columns R1 to R8?
This will work just as well:


declare @str varchar(200)
declare @mod int

select	@str = 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select	@mod   = len(@str)
select @mod

select
	password =
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)
from
(
select
	NUMBER,
	R1 = abs(convert(bigint,convert(varbinary(100),newid())))
FROM
	-- Function available in Script Library Forum
	F_TABLE_NUMBER_RANGE(1,100) aaa
) aa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-19 : 11:56:43

sequal to Password Generation Challenge ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 13:17:01

quote:

Originally posted by Arnold Fribble

Michael, why does your aa subquery have columns R1 to R8?
This will work just as well:


declare @str varchar(200)
declare @mod int

select	@str = 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select	@mod   = len(@str)
select @mod

select
	password =
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)
from
(
select
	NUMBER,
	R1 = abs(convert(bigint,convert(varbinary(100),newid())))
FROM
	-- Function available in Script Library Forum
	F_TABLE_NUMBER_RANGE(1,100) aaa
) aa

That actually depends on when SQL Server decides to evaluate the newid() function. For example, the following code will return a string of the same 8 characters. I wasn't sure if there is a order of evaluation I could depend on, so I did it with multiple columns.


declare @str varchar(200)
declare @mod int

select	@str = 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select	@mod   = len(@str)

select
	password =
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)+
	substring(@str,(R1%@mod)+1,1)
from
(
select
	top 100 percent
	NUMBER,
	R1 = abs(convert(bigint,convert(varbinary(100),newid())))
FROM
	-- Function available in Script Library Forum
	F_TABLE_NUMBER_RANGE(1,5) aaa
) aa

Results:


password 
-------- 
YYYYYYYY
QQQQQQQQ
IIIIIIII
22222222
FFFFFFFF

(5 row(s) affected)

CODO ERGO SUM

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 15:55:35

Ok im confused, why would your code ever work Arnold, or are you kidding and I just to slow?

-- The Heisenberg uncertainty principle also applies when debugging

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-09-19 : 17:09:07

quote:
Originally posted by PSamsig

Ok im confused, why would your code ever work Arnold, or are you kidding and I just to slow?

Yes, it works. At least, it does when I run it on my SQL Server 2000 installation. The fact that it does work, as Michael says, relies on when the NEWID() gets evaluated. Because SQL Server typically treats scalar operations as cheap, it tends to produce query plans that reevaluate things that one might expect to be evaluated once earlier in the plan. In this case, each reference to R1 gets expanded into the expression that contains the NEWID() and consequently NEWID() gets called 8 times.
But of course, any change (like Michael's TOP 100 PERCENT) might change the plan such that it stops working.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 17:31:40

And I tested yours unchanged query on 2005 and its a no go

-- The Heisenberg uncertainty principle also applies when debugging

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 17:36:20

I see this as a problem, since changing the order of evaluation produces different results. I don't like using NEWID() to produce random numbers because of this, but I don't know of another mechanisim for a set based operation.

There are some other issues with NEWID() that you have to be aware of. The following shows a group by with and without the TOP in the derived table. In the query without the TOP, the group by produces duplicate rows. I really consider this one to be a bug.


drop table #temp
go
select
	number
into
	#temp
from
	F_TABLE_NUMBER_RANGE(1,10000) a
go
print 'No duplicates with top'
select
	 nm = rnd%10
from
	(
	select top 100 percent
		rnd=abs(convert(bigint,convert(varbinary(20),newid() )))
	from
		#temp
	) a
group by  rnd%10
order by  rnd%10
go
print 'Gives duplicates'
select
	 nm = rnd%10
from
	(
	select 
		rnd=abs(convert(bigint,convert(varbinary(20),newid() )))
	from
		#temp
	) a
group by  rnd%10
order by  rnd%10

Results:



(10000 row(s) affected)

No duplicates with top
nm                   
-------------------- 
0
1
2
3
4
5
6
7
8
9

(10 row(s) affected)

Gives duplicates
nm                   
-------------------- 
1
3
3
3
4
5
5
5
8
8

(10 row(s) affected)

CODO ERGO SUM

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 17:44:24

It works in 2005, no dublicates, but i guess that isnt a surprise. I find it scary though, that TOP 100 PERCENT actually affects the query plan ... isnt that a bug in it self?

-- The Heisenberg uncertainty principle also applies when debugging

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources