random string functions.. - 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

SQL Server Development (2000)

random string functions..

Author

Topic

martellianz
Starting Member

2 Posts

Posted - 2006-10-02 : 04:44:59

hellow all,

i have a problem when i created a functions to make a random string,anyone can help me?thanks

CREATE FUNCTION dbo.randomstring (@len int = 8, @voucher_type char(7) = 'simple' )
returns int(8)
AS

BEGIN
DECLARE @voucher varchar(25), @type tinyint, @bitmap char(6)
SET @voucher=''
SET @bitmap = ''

WHILE @len > 0
BEGIN

BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)

IF @type = 1 --Appending a random lower case alphabet to @voucher
SET @voucher = @voucher + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @voucher
SET @voucher = @voucher + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @voucher
SET @voucher = @voucher + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @voucher
SET @voucher = @voucher + CHAR(ROUND(65 + (RAND() * (25)),0))
END

SET @len = @len - 1
END

SELECT @voucher

END
GO
----------------------------------------------------------------------
error message

Server: Msg 443, Level 16, State 1, Procedure Funcitons, Line 15
Invalid use of 'rand' within a function.
Server: Msg 443, Level 16, State 1, Procedure Funcitons, Line 18
Invalid use of 'rand' within a function.
Server: Msg 443, Level 16, State 1, Procedure Funcitons, Line 20
Invalid use of 'rand' within a function.
Server: Msg 443, Level 16, State 1, Procedure Funcitons, Line 22
Invalid use of 'rand' within a function.
Server: Msg 443, Level 16, State 1, Procedure Funcitons, Line 24
Invalid use of 'rand' within a function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-02 : 04:50:12

SELECT LEFT(NEWID(), 8)

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 06:25:11

You cannot use RAND() or NEWID() in a user-defined function.

You can pass RAND() as an input parameter, but if you use it in a select statement, RAND() will return the same value on each row.

You might be able to use this as an input parameter to work around that.

dbo.randomstring(8,'123456', rand(convert(varbinary(20),newid())) )

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-02 : 08:54:46

I'd go with Peso on this one...NewID() is as random as it gets and is pretty quick unless you generate thousands...and btw: your function returns and number, not a string and newid use letters also.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:38:30

http://sqlteam.com/forums/topic.asp?TOPIC_ID=59194

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 10:20:35

quote:
Originally posted by Lumbago

I'd go with Peso on this one...NewID() is as random as it gets and is pretty quick unless you generate thousands...and btw: your function returns and number, not a string and newid use letters also.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

You can't use NEWID() inside a function.

The following code will work, but it may not meet the business needs.

SELECT LEFT(NEWID(), 8)

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 02:44:09

There's always the possibility of passing NewID() as a parameter to the function and as he's passing several other parameters I don't see this as a problem. You might be correct that it woun't meet the business requirements but as far as I understand martellianz only requirement was to generate a random 8-character string hence the left(newid(), 8) would do the job perfectly and also save him quite a bit of coding/logic.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:00:36

8 random digits

SELECT LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NEWID(), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), '-', ''), 8)

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-03 : 05:14:00

quote:
Originally posted by Lumbago

There's always the possibility of passing NewID() as a parameter to the function and as he's passing several other parameters I don't see this as a problem. You might be correct that it woun't meet the business requirements but as far as I understand martellianz only requirement was to generate a random 8-character string hence the left(newid(), 8) would do the job perfectly and also save him quite a bit of coding/logic.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

The problem is that SELECT LEFT(NEWID(),8) produces a ramdom string from a list of only 16 chatacters, while his function was trying to pick from 88 possible characters. If is is being used to generate a password, it will be a much weaker password.

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 05:39:30

quote:
The problem is that SELECT LEFT(NEWID(),8) produces a ramdom string from a list of only 16 chatacters, while his function was trying to pick from 88 possible characters. If is is being used to generate a password, it will be a much weaker password.

This is a very good point but again it's all up to the business requirements. But I assume martellianz has gotten the info he needed on the topic...and if not the link madhi posted will give plenty of aditional info

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

martellianz
Starting Member

2 Posts

Posted - 2006-10-17 : 02:42:35

dear all,

i still can't use the functions to make a random string.sorry,but i really2 new in using SQl server,anyone can help me?thanks...please,gimme some example,bcoz i have no more idea..thanks

regards

martell

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources