Skip to content

Instantly share code, notes, and snippets.

@OllieJones
Last active February 25, 2016 21:01
Show Gist options
  • Save OllieJones/eeb1e4936527c4685dfa to your computer and use it in GitHub Desktop.
Save OllieJones/eeb1e4936527c4685dfa to your computer and use it in GitHub Desktop.
Cryptographic quality random text string generation in SQL Server
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Generate a 20-character cryptographically secure random string of uppercase letters and digits.
--- The entropy of such a string is about 100 bits: plenty.
-- Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4b5a6d1-1068-41e1-abd7-b3ec51d3f1c3/create-function-error?forum=transactsql
-- =============================================
CREATE VIEW RandomSeqView AS
SELECT seq = CRYPT_GEN_RANDOM(22);
GO
CREATE FUNCTION RandomString ()
RETURNS CHAR(20)
AS
BEGIN
DECLARE @result CHAR(20);
DECLARE @randomSeq BINARY(22);
SELECT @randomSeq = seq FROM RandomSeqView;
SELECT @result =
UPPER(LEFT(REPLACE(REPLACE(REPLACE(CAST(N'' AS XML).value(
'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
, 'VARCHAR(MAX)'
),'=',''),'+',''),'/','')+'ABCDEFGHIJKLMNOPQRST',20))
FROM (
SELECT @randomSeq AS bin
) AS bin_sql_server_temp;
RETURN @result;
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment