SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Generating Random String
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

oitsubob
Yak Posting Veteran

USA
70 Posts

Posted - 09/12/2006 :  20:38:26  Show Profile  Reply with Quote
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

New Zealand
58 Posts

Posted - 09/12/2006 :  21:05:14  Show Profile  Reply with Quote
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

USA
70 Posts

Posted - 09/13/2006 :  00:34:35  Show Profile  Reply with Quote
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)

Singapore
17586 Posts

Posted - 09/13/2006 :  00:37:13  Show Profile  Reply with Quote
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


Edited by - khtan on 09/13/2006 00:42:25
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 09/13/2006 :  00:39:56  Show Profile  Reply with Quote
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

USA
70 Posts

Posted - 09/13/2006 :  02:05:15  Show Profile  Reply with Quote
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

USA
98 Posts

Posted - 09/13/2006 :  09:04:39  Show Profile  Send rme8494 a Yahoo! Message  Reply with Quote
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)

USA
7020 Posts

Posted - 09/13/2006 :  10:28:26  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 09/13/2006 12:25:43
Go to Top of Page

oitsubob
Yak Posting Veteran

USA
70 Posts

Posted - 09/13/2006 :  12:11:46  Show Profile  Reply with Quote
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

USA
70 Posts

Posted - 09/13/2006 :  14:17:58  Show Profile  Reply with Quote
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

United Kingdom
1 Posts

Posted - 09/19/2006 :  10:40:02  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/19/2006 :  10:57:24  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/19/2006 :  11:15:07  Show Profile  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 09/19/2006 :  11:48:57  Show Profile  Reply with Quote
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


Edited by - Arnold Fribble on 09/19/2006 11:53:52
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 09/19/2006 :  11:56:43  Show Profile  Reply with Quote
sequal to Password Generation Challenge ?


KH

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/19/2006 :  13:17:01  Show Profile  Reply with Quote
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

Denmark
384 Posts

Posted - 09/19/2006 :  15:55:35  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 09/19/2006 :  17:09:07  Show Profile  Reply with Quote
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

Denmark
384 Posts

Posted - 09/19/2006 :  17:31:40  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
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)

USA
7020 Posts

Posted - 09/19/2006 :  17:36:20  Show Profile  Reply with Quote
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

Denmark
384 Posts

Posted - 09/19/2006 :  17:44:24  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000