Blog Post

A Variable Length Random String

,

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