fnGenerateRandomString

SQL function to generate random string
I thought it would be easier to find something like this online, but did not find it, so, faster for me to create new one.
Uses a view, create view code is already found below.
Currently is generating random with upper case and numbers only, uncomment lines indicated to enable other options, like lower case
To use:
select schema.fnGenerateRandomString(15)

DROP VIEW [schema].[vwGetNewId]
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [schema].[vwGetNewId]
AS
SELECT NEWID() AS Id
GO


DROP FUNCTION [schema].[fnGenerateRandomString]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [schema].[fnGenerateRandomString](@length INT = 8)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @result CHAR(2000);
DECLARE @String VARCHAR(2000);
SET @String =
--'abcdefghijklmnopqrstuvwxyz' + --lower letters
--'ABCDEFGHIJKLMNOPQRSTUVWXYZ' + --upper letters
--'1234567890'; --number characters
'ABCDEFGHJKLMNPQRSTUVWXYZ' + --upper letters
'23456789'; --number characters

SELECT @result = (
SELECT TOP (@length) SUBSTRING(@String, 1 + number, 1) AS [text()]
FROM master..spt_values
WHERE number < DATALENGTH(@String) AND type = 'P'
ORDER BY (
  SELECT TOP 1 Id FROM [vwGetNewId]
  ) --instead of using newid()
FOR XML PATH('')
 );
 RETURN @result;
END;
GO

$length

select schema.fnGenerateRandomString(15)

Views 531 Downloads 247


ANmarAmdeen
592
Attachments
Math SQL
Revisions

v1.0