A Variable Length Random String

Lynn Pettis, 2009-04-04

I was looking through some of the active threads on SQLServerCentral.com tonight, and I ran across one with some code that looked familiar.  While reading this thread, I noticed that the function returned a fixed length random character string.  This told me that if you needed a random character string of a different length, you’d need another function.

How can you get around this?  You can’t use the function NEWID() in a function, SQL Server 2005 doesn’t like it.  There is a way to do this.  Create a view that returns the result of the function NEWID(), and use that in your function to return a random string of different lengths.

 

Here is the code I put together:

create view dbo.MyNewID as

select newid() as NewIDValue;

go

create function dbo.ufn_RandomString(

@pStringLength int = 20

) returns varchar(max)

as begin

declare @RandomString varchar(max);

with

a1 as (select 1 as N union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select

row_number() over (order by N) as N

from

a4)

, cteRandomString (

RandomString

) as (

select top (@pStringLength)

substring(x,(abs(checksum((select NewIDValue from MyNewID)))%36)+1,1)

from

Tally cross join (select x=‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’) a

)

select @RandomString =

replace((select

‘,’ + RandomString

from

cteRandomString

for xml path ()),‘,’,);

return (@RandomString);

end

go

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads