If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?
30 Answers
Another simple solution with the complete alphabet:
SELECT LEFT(REPLACE(REPLACE((SELECT CRYPT_GEN_RANDOM(16) FOR XML PATH(''), BINARY BASE64),'+',''),'/',''),16);
Replace the two 16's with the desired length.
Sample result:
pzyMATe3jJwN1XkB
For SQL Server 2016 and later, here is a really simple and relatively efficient expression to generate cryptographically random strings of a given byte length:
--Generates 36 bytes (48 characters) of base64 encoded random data
select r from OpenJson((select Crypt_Gen_Random(36) r for json path))
with (r varchar(max))
Note that the byte length is not the same as the encoded size; use the following from this article to convert:
Bytes = 3 * (LengthInCharacters / 4) - Padding
This will produce a string 96 characters in length, from the Base64 range (uppers, lowers, numbers, + and /). Adding 3 "NEWID()" will increase the length by 32, with no Base64 padding (=).
SELECT
CAST(
CONVERT(NVARCHAR(MAX),
CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
,2)
AS XML).value('xs:base64Binary(xs:hexBinary(.))', 'VARCHAR(MAX)') AS StringValue
If you are applying this to a set, make sure to introduce something from that set so that the NEWID() is recomputed, otherwise you'll get the same value each time:
SELECT
U.UserName
, LEFT(PseudoRandom.StringValue, LEN(U.Pwd)) AS FauxPwd
FROM Users U
CROSS APPLY (
SELECT
CAST(
CONVERT(NVARCHAR(MAX),
CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), U.UserID) -- Causes a recomute of all NEWID() calls
,2)
AS XML).value('xs:base64Binary(xs:hexBinary(.))', 'VARCHAR(MAX)') AS StringValue
) PseudoRandom
CREATE OR ALTER PROC USP_GENERATE_RANDOM_CHARACTER ( @NO_OF_CHARS INT, @RANDOM_CHAR VARCHAR(40) OUTPUT) AS BEGIN
SELECT @RANDOM_CHAR = SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS)
END /* USAGE: DECLARE @OUT VARCHAR(40) EXEC USP_GENERATE_RANDOM_CHARACTER 13,@RANDOM_CHAR = @OUT OUTPUT SELECT @OUT */
Small modification of Remus Rusanu code -thanks for sharing
This generate a random string and can be used without the seed value
declare @minLen int = 1, @maxLen int = 612, @string varchar(8000);
declare @length int;
declare @seed INT
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)
declare @step bigint = rand() * 2147483647;
select @alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
, @digit = '1234567890'
, @specials = '_@#-/\ '
select @first = @alpha + '_@';
set @seed = (rand(@step)*2147483647);
select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
declare @dice int;
select @dice = rand(@seed) * len(@first),
@seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = substring(@first, @dice, 1);
while 0 < @length
begin
select @dice = rand(@seed) * 100
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
if (@dice < 10) -- 10% special chars
begin
select @dice = rand(@seed) * len(@specials)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@specials, @dice, 1);
end
else if (@dice < 10+10) -- 10% digits
begin
select @dice = rand(@seed) * len(@digit)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@digit, @dice, 1);
end
else -- rest 80% alpha
begin
declare @preseed int = @seed;
select @dice = rand(@seed) * len(@alpha)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@alpha, @dice, 1);
end
select @length = @length - 1
end
SELECT @string
In SQL Server 2012+ we could concatenate the binaries of some (G)UIDs and then do a base64 conversion on the result.
SELECT
textLen.textLen
, left((
select CAST(newid() as varbinary(max)) + CAST(newid() as varbinary(max))
where textLen.textLen is not null /*force evaluation for each outer query row*/
FOR XML PATH(''), BINARY BASE64
),textLen.textLen) as randomText
FROM ( values (2),(4),(48) ) as textLen(textLen) --define lengths here
;
If you need longer strings (or you see = characters in the result) you need to add more + CAST(newid() as varbinary(max)) in the sub select.
So I liked a lot of the answers above, but I was looking for something that was a little more random in nature. I also wanted a way to explicitly call out excluded characters. Below is my solution using a view that calls the CRYPT_GEN_RANDOM to get a cryptographic random number. In my example, I only chose a random number that was 8 bytes. Please note, you can increase this size and also utilize the seed parameter of the function if you want. Here is the link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql
CREATE VIEW [dbo].[VW_CRYPT_GEN_RANDOM_8]
AS
SELECT CRYPT_GEN_RANDOM(8) as [value];
The reason for creating the view is because CRYPT_GEN_RANDOM cannot be called directly from a function.
From there, I created a scalar function that accepts a length and a string parameter that can contain a comma delimited string of excluded characters.
CREATE FUNCTION [dbo].[fn_GenerateRandomString]
(
@length INT,
@excludedCharacters VARCHAR(200) --Comma delimited string of excluded characters
)
RETURNS VARCHAR(Max)
BEGIN
DECLARE @returnValue VARCHAR(Max) = ''
, @asciiValue INT
, @currentCharacter CHAR;
--Optional concept, you can add default excluded characters
SET @excludedCharacters = CONCAT(@excludedCharacters,',^,*,(,),-,_,=,+,[,{,],},\,|,;,:,'',",<,.,>,/,`,~');
--Table of excluded characters
DECLARE @excludedCharactersTable table([asciiValue] INT);
--Insert comma
INSERT INTO @excludedCharactersTable SELECT 44;
--Stores the ascii value of the excluded characters in the table
INSERT INTO @excludedCharactersTable
SELECT ASCII(TRIM(value))
FROM STRING_SPLIT(@excludedCharacters, ',')
WHERE LEN(TRIM(value)) = 1;
--Keep looping until the return string is filled
WHILE(LEN(@returnValue) < @length)
BEGIN
--Get a truly random integer values from 33-126
SET @asciiValue = (SELECT TOP 1 (ABS(CONVERT(INT, [value])) % 94) + 33 FROM [dbo].[VW_CRYPT_GEN_RANDOM_8]);
--If the random integer value is not in the excluded characters table then append to the return string
IF(NOT EXISTS(SELECT *
FROM @excludedCharactersTable
WHERE [asciiValue] = @asciiValue))
BEGIN
SET @returnValue = @returnValue + CHAR(@asciiValue);
END
END
RETURN(@returnValue);
END
Below is an example of the how to call the function.
SELECT [dbo].[fn_GenerateRandomString](8,'!,@,#,$,%,&,?');
Here's one I came up with today (because I didn't like any of the existing answers enough).
This one generates a temp table of random strings, is based off of newid(), but also supports a custom character set (so more than just 0-9 & A-F), custom length (up to 255, limit is hard-coded, but can be changed), and a custom number of random records.
Here's the source code (hopefully the comments help):
/**
* First, we're going to define the random parameters for this
* snippet. Changing these variables will alter the entire
* outcome of this script. Try not to break everything.
*
* @var {int} count The number of random values to generate.
* @var {int} length The length of each random value.
* @var {char(62)} charset The characters that may appear within a random value.
*/
-- Define the parameters
declare @count int = 10
declare @length int = 60
declare @charset char(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
/**
* We're going to define our random table to be twice the maximum
* length (255 * 2 = 510). It's twice because we will be using
* the newid() method, which produces hex guids. More later.
*/
-- Create the random table
declare @random table (
value nvarchar(510)
)
/**
* We'll use two characters from newid() to make one character in
* the random value. Each newid() provides us 32 hex characters,
* so we'll have to make multiple calls depending on length.
*/
-- Determine how many "newid()" calls we'll need per random value
declare @iterations int = ceiling(@length * 2 / 32.0)
/**
* Before we start making multiple calls to "newid", we need to
* start with an initial value. Since we know that we need at
* least one call, we will go ahead and satisfy the count.
*/
-- Iterate up to the count
declare @i int = 0 while @i < @count begin set @i = @i + 1
-- Insert a new set of 32 hex characters for each record, limiting to @length * 2
insert into @random
select substring(replace(newid(), '-', ''), 1, @length * 2)
end
-- Now fill the remaining the remaining length using a series of update clauses
set @i = 0 while @i < @iterations begin set @i = @i + 1
-- Append to the original value, limit @length * 2
update @random
set value = substring(value + replace(newid(), '-', ''), 1, @length * 2)
end
/**
* Now that we have our base random values, we can convert them
* into the final random values. We'll do this by taking two
* hex characters, and mapping then to one charset value.
*/
-- Convert the base random values to charset random values
set @i = 0 while @i < @length begin set @i = @i + 1
/**
* Explaining what's actually going on here is a bit complex. I'll
* do my best to break it down step by step. Hopefully you'll be
* able to follow along. If not, then wise up and come back.
*/
-- Perform the update
update @random
set value =
/**
* Everything we're doing here is in a loop. The @i variable marks
* what character of the final result we're assigning. We will
* start off by taking everything we've already done first.
*/
-- Take the part of the string up to the current index
substring(value, 1, @i - 1) +
/**
* Now we're going to convert the two hex values after the index,
* and convert them to a single charset value. We can do this
* with a bit of math and conversions, so function away!
*/
-- Replace the current two hex values with one charset value
substring(@charset, convert(int, convert(varbinary(1), substring(value, @i, 2), 2)) * (len(@charset) - 1) / 255 + 1, 1) +
-- (1) -------------------------------------------------------^^^^^^^^^^^^^^^^^^^^^^^-----------------------------------------
-- (2) ---------------------------------^^^^^^^^^^^^^^^^^^^^^^11111111111111111111111^^^^-------------------------------------
-- (3) --------------------^^^^^^^^^^^^^2222222222222222222222222222222222222222222222222^------------------------------------
-- (4) --------------------333333333333333333333333333333333333333333333333333333333333333---^^^^^^^^^^^^^^^^^^^^^^^^^--------
-- (5) --------------------333333333333333333333333333333333333333333333333333333333333333^^^4444444444444444444444444--------
-- (6) --------------------5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555^^^^----
-- (7) ^^^^^^^^^^^^^^^^^^^^66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666^^^^
/**
* (1) - Determine the two hex characters that we'll be converting (ex: 0F, AB, 3C, etc.)
* (2) - Convert those two hex characters to a a proper hexadecimal (ex: 0x0F, 0xAB, 0x3C, etc.)
* (3) - Convert the hexadecimals to integers (ex: 15, 171, 60)
* (4) - Determine the conversion ratio between the length of @charset and the range of hexadecimals (255)
* (5) - Multiply the integer from (3) with the conversion ratio from (4) to get a value between 0 and (len(@charset) - 1)
* (6) - Add 1 to the offset from (5) to get a value between 1 and len(@charset), since strings start at 1 in SQL
* (7) - Use the offset from (6) and grab a single character from @subset
*/
/**
* All that is left is to add in everything we have left to do.
* We will eventually process the entire string, but we will
* take things one step at a time. Round and round we go!
*/
-- Append everything we have left to do
substring(value, 2 + @i, len(value))
end
-- Select the results
select value
from @random
It's not a stored procedure, but it wouldn't be that hard to turn it into one. It's also not horrendously slow (it took me ~0.3 seconds to generate 1,000 results of length 60, which is more than I'll ever personally need), which was one of my initial concerns from all of the string mutation I'm doing.
The main takeaway here is that I'm not trying to create my own random number generator, and my character set isn't limited. I'm simply using the random generator that SQL has (I know there's rand(), but that's not great for table results). Hopefully this approach marries the two kinds of answers here, from overly simple (i.e. just newid()) and overly complex (i.e. custom random number algorithm).
It's also short (minus the comments), and easy to understand (at least for me), which is always a plus in my book.
However, this method cannot be seeded, so it's going to be truly random each time, and you won't be able to replicate the same set of data with any means of reliability. The OP didn't list that as a requirement, but I know that some people look for that sort of thing.
I know I'm late to the party here, but hopefully someone will find this useful.
Based on various helpful responses in this article I landed with a combination of a couple options I liked.
DECLARE @UserId BIGINT = 12345 -- a uniqueId in my system
SELECT LOWER(REPLACE(NEWID(),'-','')) + CONVERT(VARCHAR, @UserId)
Below are the way to Generate 4 Or 8 Characters Long Random Alphanumeric String in SQL
select LEFT(CONVERT(VARCHAR(36),NEWID()),4)+RIGHT(CONVERT(VARCHAR(36),NEWID()),4)
SELECT RIGHT(REPLACE(CONVERT(VARCHAR(36),NEWID()),'-',''),8)
it's very simply.use it and enjoy.
CREATE VIEW [dbo].[vwGetNewId]
AS
SELECT NEWID() AS Id
Creat FUNCTION [dbo].[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
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 dbo.vwGetNewId
) --instead of using newid()
FOR XML PATH('')
);
RETURN @result;
END;
Related