SQL and the like

Another TSQL random length random string generator

The following function function will return you a random string of the specified characters, for a length of between @StrLenLo and @StrLenHi.  The only ‘Oddity’ with using this function is that if the parameters are not dependant upon data within a table SQLServer will create a hash join which will cause the same value to be returned.  This is the reason for the bizzare looking  ‘case when Num>=0 then 8 else 8 end’

First off generate a ‘numbers’ table

CREATE TABLE dbo.Numbers (Num INT NOT NULL PRIMARY KEY CLUSTERED);
GO
INSERT INTO dbo.Numbers(Num)

 SELECT n
 FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY (select 1) )
               FROM sys.columns a cross join sys.columns b ) D ( n )
     WHERE n <= 1000;
go
update statistics Numbers with fullscan
go

Then , as SqlServer does not allow the use of newid() within functions create a small view that will return a random integer.

drop View VwNewCheck
go
Create View VwNewCheck
with schemabinding
as
Select abs(checksum(NewId()))  as New_Id
go

Next up the actual function

Drop Function GetVariableLengthRandomCode
go

Create Function GetVariableLengthRandomCode(@StrLenLo integer,@StrLenHi integer,@CharsNeeded char(62))
returns table
with schemabinding
as
return
(
with cteRandomLength(StrLen)
as
(
   Select @StrLenLo + VwNewCheck.new_id%((@StrLenHi+1)-@StrLenLo)
     from dbo.VwNewCheck
),
cteRandomChars(num,c)
as
(
   Select Num,substring(@CharsNeeded,(Select VwNewCheck.new_id%(len(@CharsNeeded)-1)+1 from dbo.VwNewCheck  where num = num ),1)
     from dbo.numbers
    where Num <= (Select StrLen from cteRandomLength)
     
)
select (
select c as [text()] 
 from  cteRandomChars
 for xml path('')) as random
)
go

And you are good to go.

select * from GetVariableLengthRandomCode(8,16,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')

As mentioned above if you execute

select  Random from numbers cross apply GetVariableLengthRandomCode(8,16,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')

Then the same value will be returned a thousand times,  so fool the optimizer by

select  Random 
   from numbers 
   cross apply GetVariableLengthRandomCode(case when Num>=0 then 8 else 8 end,16,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')

and you will get a thousand random strings between 8 and 16 characters long.

Read the complete post at http://feedproxy.google.com/~r/SqlAndTheLike/~3/excCilgDHFc/faster-tsql-random-length-random-string.html