SQL Server 2005 function to create a random string

sql-server

After reading this nice post about a stored procedure to create random numbers i figured i needed a function for more flexibility. Here’s how to create a user defined function to generate some random characters with a length of up to 100 characters.

Example:

SELECT dbo.CreateRandomString(10)

comes up with: H34ja1xQMm

First we need to create a simple view that is being used by our UDF:

create view [dbo].[RandHelper]
as select rand( ) as r
GO

And now our CreateRandomString UDF:

CREATE function [dbo].[CreateRandomString] (
 @passwordLength as smallint
)
RETURNS varchar(100)

AS

begin
	declare @password varchar(100)
	declare @characters varchar(100)
	declare @count int

	set @characters = ''

	 -- load up numbers 0 - 9
	 set @count = 48
	 while @count <=57
	 begin
	     set @characters = @characters + Cast(CHAR(@count) as char(1))
	     set @count = @count + 1
	 end

	 -- load up uppercase letters A - Z
	 set @count = 65
	 while @count <=90
	 begin
	     set @characters = @characters + Cast(CHAR(@count) as char(1))
	     set @count = @count + 1
	 end

	 -- load up lowercase letters a - z
	 set @count = 97
	 while @count <=122
	 begin
	     set @characters = @characters + Cast(CHAR(@count) as char(1))
	     set @count = @count + 1
	 end


	set @count = 0
	set @password = ''


	 while @count < @passwordLength
	 begin
	     set @password = @password + SUBSTRING(@characters,CAST(CEILING((SELECT r FROM RandHelper)*LEN(@characters)) as int),1)
	     set @count = @count + 1
	 end

	RETURN @password
end
share