| Author |
 Topic  |
|
oitsubob
Yak Posting Veteran
USA
70 Posts |
 Posted - 09/12/2006 : 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
New Zealand
58 Posts |
Posted - 09/12/2006 : 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
USA
70 Posts |
Posted - 09/13/2006 : 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)
Singapore
16746 Posts |
Posted - 09/13/2006 : 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
|
Edited by - khtan on 09/13/2006 00:42:25 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
|
|
oitsubob
Yak Posting Veteran
USA
70 Posts |
Posted - 09/13/2006 : 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
USA
98 Posts |
Posted - 09/13/2006 : 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)
USA
6997 Posts |
Posted - 09/13/2006 : 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 |
Edited by - Michael Valentine Jones on 09/13/2006 12:25:43 |
|
|
|
oitsubob
Yak Posting Veteran
USA
70 Posts |
Posted - 09/13/2006 : 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
USA
70 Posts |
Posted - 09/13/2006 : 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
United Kingdom
1 Posts |
Posted - 09/19/2006 : 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)
USA
6997 Posts |
Posted - 09/19/2006 : 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)
USA
6997 Posts |
Posted - 09/19/2006 : 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
United Kingdom
1961 Posts |
Posted - 09/19/2006 : 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
 |
Edited by - Arnold Fribble on 09/19/2006 11:53:52 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 09/19/2006 : 11:56:43
|
sequal to Password Generation Challenge ? 
KH
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/19/2006 : 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
Denmark
382 Posts |
Posted - 09/19/2006 : 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
United Kingdom
1961 Posts |
Posted - 09/19/2006 : 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
Denmark
382 Posts |
Posted - 09/19/2006 : 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)
USA
6997 Posts |
Posted - 09/19/2006 : 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
Denmark
382 Posts |
Posted - 09/19/2006 : 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 |
|
|
|
Topic |
|
|
|
Generating Random String
