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 164 Downloads 65

'sql', 'random', 'string', 'password', 'id', 'uid', 'iden', 'identification', 'identity', 'text', 'randomize', 'function', 'mssql', 'generate', 'view', 'table', 'database'

ANmarAmdeen
520
Attachments
Math SQL
Revisions

v1.0