The following will generate a random fixed-length string using SQL. Simply pass in a string of accepted characters and the length of the random string to be generated.
IF ISNULL(object_id('cspRandomString'),0)<>0 DROP PROC cspRandomString
IF ISNULL(object_id('fnGenRandomInteger'),0)<>0 DROP FUNCTION fnGenRandomInteger
GO
CREATE function fnGenRandomInteger(@MinValue int, @MaxValue int, @Seed float)
returns int
as
begin
-- =============================================
-- Author: Loh Hon Chun
-- =============================================
return ((@MaxValue + 1) - @MinValue) * @Seed + @MinValue
end
go
CREATE procedure cspRandomString (
@Result varchar(255) output,
@AcceptedChars varchar(255),
@Length int
)
as
begin
-- =============================================
-- Author: Loh Hon Chun
-- =============================================
set nocount on
declare @ret char(255)
declare @i int, @pos int
set @Result = ''
set @i = 0
if LEN(@AcceptedChars) > 0
begin
while @i < @Length
begin
set @pos = dbo.fnGenRandomInteger(1, LEN(@AcceptedChars), RAND(CONVERT(VARBINARY, NEWID())))
set @Result = @Result + CAST(SUBSTRING(@AcceptedChars, @pos, 1) AS VARCHAR(1))
set @i = @i + 1
end
end
end
go
declare @result varchar(255)
declare @acceptedChars varchar(255)
declare @length int
set @acceptedChars = '0123456789'
set @length = 7
exec cspRandomString @Result=@result output, @AcceptedChars=@acceptedChars, @Length=@length
print @result
go