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

General SQL Server Forums

Script Library

Random String

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-15 : 10:40:47

I used this to generate a random password. You can change what it returns to up to an 8000 character password if you feel brave. You will need this function (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14924) to generate the random numbers. For some reason, occassionaly a NULL (CHAR(0)) character would get in there and mess things up so I had to remove those. You could change this to even include the non alpha numeric characters.


create procedure random_password 
    @length smallint,
    @password nvarchar(256) = null output
as
    set nocount on

    declare @number int
    declare @lower int
    declare @upper int
    declare @part tinyint

    if @length > 256 set @length = 256
    set @password = ''
    
    while len(@password) < @length
        begin
            exec random_number 0, 3, @part output

            if @part = 0
                select @lower = ascii('a'),
                       @upper = ascii('z')
            else if @part = 1
                select @lower = ascii('A'),
                       @upper = ascii('Z')
            else if @part = 2
                select @lower = ascii('0'),
                       @upper = ascii('9')

            set @part = null
            set @number = null
            exec random_number @lower, @upper, @number output
            set @password = @password + ltrim(char(@number))
            if substring(@password, len(@password), 1) = char(0) set @password = left(@password, len(@password) - 1)
        end
go

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-04-15 : 11:10:14

I have a simple one at: http://vyaskn.tripod.com/code.htm#randp

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-15 : 11:27:28

I think I'd favour having an explicit list of characters that can be generated. The problem with the proc as it stands is that it will generate all of I, l and 1, and both 0 and O. In Verdana or Andale Mono, that looks ok, but 1 and l are difficult to distinguish in Courier, and I and l are identical in MS Sans 8pt. For a font with a particularly large x-height or old-style digits (e.g. Georgia) it would be worth leaving out 'o' too.


create procedure random_password 
    @length smallint,
    @password nvarchar(256) = null output
as
    set nocount on

    declare @number int
    declare @chars nvarchar(256)
    declare @nchars int

    set @chars = N'23456789abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ'
    set @nchars = len(@chars)

    if @length > 256 set @length = 256
    set @password = N''
    
    while len(@password) < @length
        begin
            exec random_number 1, @nchars, @number output
            set @password = @password + substring(@chars, @number,1)
        end
go

Edited by - Arnold Fribble on 04/15/2002 11:29:32

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-16 : 12:24:27

that's a good way too... it was more so meant just to generate a random string of characters XXXXX in length... you can't imagine how long (5 minutes) it took me to get the random_number procedure to actually generate a random number .... damn friggen rand() function...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources