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
Go to Top of Page

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
Go to Top of Page

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)



KH

Go to Top of Page

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


KH

Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-19 : 11:56:43
sequal to Password Generation Challenge ?


KH

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -