使用 T-SQL 生成随机字符串

小开

我首先遇到了 this blog post，然后想出了下面这个存储过程，我在当前项目中使用它(对不起，格式有点奇怪) :

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”


WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))


IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

小开

在 SQL 2000中，我创建了一个包含我想要使用的字符的表，创建了一个视图，通过 newid ()从该表中选择字符，然后从该视图中选择前1个字符。

CREATE VIEW dbo.vwCodeCharRandom
AS
SELECT TOP 100 PERCENT
CodeChar
FROM dbo.tblCharacter
ORDER BY
NEWID()


...


SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom

然后您可以简单地从视图中提取字符，并根据需要将它们连接起来。

编辑: 受到斯蒂芬回应的启发..。

select top 1 RandomChar from tblRandomCharacters order by newid()

No need for a view (in fact I'm not sure why I did that - the code's from several years back). You can still specify the characters you want to use in the table.

小开

Using a guid

SELECT @randomString = CONVERT(varchar(255), NEWID())

很短。

小开

与第一个例子类似，但更具灵活性:

-- min_length = 8, max_length = 12
SET @Length = RAND() * 5 + 8
-- SET @Length = RAND() * (max_length - min_length + 1) + min_length


-- define allowable character explicitly - easy to read this way an easy to
-- omit easily confused chars like l (ell) and 1 (one) or 0 (zero) and O (oh)
SET @CharPool =
'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
SET @PoolLength = Len(@CharPool)


SET @LoopCount = 0
SET @RandomString = ''


WHILE (@LoopCount < @Length) BEGIN
SELECT @RandomString = @RandomString +
SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1)
SELECT @LoopCount = @LoopCount + 1
END

我忘了提到另外一个让这个更加灵活的特性。通过重复@CharPool 中的字符块，可以增加某些字符的权重，以便更有可能选择它们。

小开

最佳答案

在生成随机数据时，特别是测试数据，使数据具有随机性是非常有用的，但是是可重现的。秘诀是对随机函数使用显式的种子，这样当用相同的种子再次运行测试时，它会再次产生完全相同的字符串。下面是一个简化的函数示例，该函数以可重现的方式生成对象名称:

alter procedure usp_generateIdentifier
@minLen int = 1
, @maxLen int = 256
, @seed int output
, @string varchar(8000) output
as
begin
set nocount on;
declare @length int;
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)
declare @step bigint = rand(@seed) * 2147483647;


select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
, @digit = '1234567890'
, @specials = '_@# '
select @first = @alpha + '_@';


set  @seed = (rand((@seed+@step)%2147483647)*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
end
go

在运行测试时，调用者生成一个与测试运行关联的随机种子(将其保存在结果表中) ，然后传递种子，类似于下面这样:

declare @seed int;
declare @string varchar(256);


select @seed = 1234; -- saved start seed


exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;

更新2016-02-17: 请看下面的评论，原始程序在推进随机种子的方式上有一个问题。我更新了代码，并且修复了前面提到的 off-by-one 问题。

小开

使用以下代码返回一个短字符串:

SELECT SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)

小开

这是一个随机的阿尔法数字生成器

print left(replace(newid(),'-',''),@length) //--@length is the length of random Num.

小开

这是根据新身份证改编的。

with list as
(
select 1 as id,newid() as val
union all
select id + 1,NEWID()
from    list
where   id + 1 < 10
)
select ID,val from list
option (maxrecursion 0)

小开

This worked for me: I needed to generate just three random alphanumeric characters for an ID, but it could work for any length up to 15 or so.

declare @DesiredLength as int = 3;
select substring(replace(newID(),'-',''),cast(RAND()*(31-@DesiredLength) as int),@DesiredLength);

小开

I thought I'd share, or give back to the community ... 它是基于 ASCII 的，这个解决方案并不完美，但是工作得很好。好好享受吧, Goran B.

/*
-- predictable masking of ascii chars within a given decimal range
-- purpose:
--    i needed an alternative to hashing alg. or uniqueidentifier functions
--    because i wanted to be able to revert to original char set if possible ("if", the operative word)
-- notes: wrap below in a scalar function if desired (i.e. recommended)
-- by goran biljetina (2014-02-25)
*/


declare
@length int
,@position int
,@maskedString varchar(500)
,@inpString varchar(500)
,@offsetAsciiUp1 smallint
,@offsetAsciiDown1 smallint
,@ipOffset smallint
,@asciiHiBound smallint
,@asciiLoBound smallint




set @ipOffset=null
set @offsetAsciiUp1=1
set @offsetAsciiDown1=-1
set @asciiHiBound=126 --> up to and NOT including
set @asciiLoBound=31 --> up from and NOT including


SET @inpString = '{"config":"some string value", "boolAttr": true}'
SET @length = LEN(@inpString)


SET @position = 1
SET @maskedString = ''


--> MASK:
---------
WHILE (@position < @length+1) BEGIN
SELECT @maskedString = @maskedString +
ISNULL(
CASE
WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
THEN
CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
(case when @ipOffset is null then
case when ASCII(SUBSTRING(@inpString,@position,1))%2=0 then @offsetAsciiUp1 else @offsetAsciiDown1 end
else @ipOffset end))
WHEN ASCII(SUBSTRING(@inpString,@position,1))<=@asciiLoBound
THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))+1000)+')' --> wrap for decode
WHEN ASCII(SUBSTRING(@inpString,@position,1))>=@asciiHiBound
THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@inpString,@position,1))+1000)+')' --> wrap for decode
END
,'')
SELECT @position = @position + 1
END


select @MaskedString




SET @inpString = @maskedString
SET @length = LEN(@inpString)


SET @position = 1
SET @maskedString = ''


--> UNMASK (Limited to within ascii lo-hi bound):
-------------------------------------------------
WHILE (@position < @length+1) BEGIN
SELECT @maskedString = @maskedString +
ISNULL(
CASE
WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
THEN
CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
(case when @ipOffset is null then
case when ASCII(SUBSTRING(@inpString,@position,1))%2=1 then @offsetAsciiDown1 else @offsetAsciiUp1 end
else @ipOffset*(-1) end))
ELSE ''
END
,'')
SELECT @position = @position + 1
END


select @maskedString

小开

If you are running SQL Server 2008 or greater, you could use the new cryptographic function crypt_gen_random() and then use base64 encoding to make it a string. This will work for up to 8000 characters.

declare @BinaryData varbinary(max)
, @CharacterData varchar(max)
, @Length int = 2048


set @BinaryData=crypt_gen_random (@Length)


set @CharacterData=cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryData"))', 'varchar(max)')


print @CharacterData

小开

select left(NEWID(),5)

这将返回 guid 字符串最左边的5个字符

Example run
------------
11C89
9DB02

小开

我意识到这是一个有很多好答案的老问题。然而，当我发现这一点，我也发现了一个更新的文章在 TechNet 的赛义德哈萨尼

如何生成随机密码

虽然解决方案的重点是密码，但它适用于一般情况。赛义德通过各种考虑来达成解决方案。非常有教育意义。

包含文章中所有代码块的脚本可以通过 TechNet 画廊单独获得，但是我肯定会从文章开始。

小开

这使用 rand 和一个种子，就像另一个应答一样，但是没有必要在每次调用时都提供一个种子。在第一次调用时提供它就足够了。

这是我修改过的代码。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'usp_generateIdentifier'))
DROP PROCEDURE usp_generateIdentifier
GO


create procedure usp_generateIdentifier
@minLen int = 1
, @maxLen int = 256
, @seed int output
, @string varchar(8000) output
as
begin
set nocount on;
declare @length int;
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)


select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
, @digit = '1234567890'
, @specials = '_@#$&'
select @first = @alpha + '_@';


-- Establish our rand seed and store a new seed for next time
set  @seed = (rand(@seed)*2147483647);


select @length = @minLen + rand() * (@maxLen-@minLen);
--print @length


declare @dice int;
select @dice = rand() * len(@first);
select @string = substring(@first, @dice, 1);


while 0 < @length
begin
select @dice = rand() * 100;
if (@dice < 10) -- 10% special chars
begin
select @dice = rand() * len(@specials)+1;
select @string = @string + substring(@specials, @dice, 1);
end
else if (@dice < 10+10) -- 10% digits
begin
select @dice = rand() * len(@digit)+1;
select @string = @string + substring(@digit, @dice, 1);
end
else -- rest 80% alpha
begin
select @dice = rand() * len(@alpha)+1;


select @string = @string + substring(@alpha, @dice, 1);
end


select @length = @length - 1;
end
end
go

小开

I'm not expert in T-SQL, but the simpliest way I've already used it's like that:

select char((rand()*25 + 65))+char((rand()*25 + 65))

这会产生两个字符(A-Z，在 ascii 65-90中)。

小开

有很多不错的答案，但是到目前为止，没有一个答案允许定制字符池并作为列的默认值。我希望能够做这样的事情:

alter table MY_TABLE add MY_COLUMN char(20) not null
default dbo.GenerateToken(crypt_gen_random(20))

所以我想到了这个，如果你修改它，要小心硬编码的数字32。

-- Converts a varbinary of length N into a varchar of length N.
-- Recommend passing in the result of CRYPT_GEN_RANDOM(N).
create function GenerateToken(@randomBytes varbinary(max))
returns varchar(max) as begin


-- Limit to 32 chars to get an even distribution (because 32 divides 256) with easy math.
declare @allowedChars char(32);
set @allowedChars = 'abcdefghijklmnopqrstuvwxyz012345';


declare @oneByte tinyint;
declare @oneChar char(1);
declare @index int;
declare @token varchar(max);


set @index = 0;
set @token = '';


while @index < datalength(@randomBytes)
begin
-- Get next byte, use it to index into @allowedChars, and append to @token.
-- Note: substring is 1-based.
set @index = @index + 1;
select @oneByte = convert(tinyint, substring(@randomBytes, @index, 1));
select @oneChar = substring(@allowedChars, 1 + (@oneByte % 32), 1); -- 32 is the number of @allowedChars
select @token = @token + @oneChar;
end


return @token;


end

小开

我使用这个程序，我开发的简单规定的字符，你想要能够显示在输入变量，你可以定义的长度太。希望这个格式好，我是新的堆栈溢出。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'GenerateARandomString'))
DROP PROCEDURE GenerateARandomString
GO


CREATE PROCEDURE GenerateARandomString
(
@DESIREDLENGTH         INTEGER = 100,
@NUMBERS               VARCHAR(50)
= '0123456789',
@ALPHABET              VARCHAR(100)
='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
@SPECIALS              VARCHAR(50)
= '_=+-$£%^&*()"!@~#:',
@RANDOMSTRING          VARCHAR(8000)   OUT


)


AS


BEGIN
-- Author David Riley
-- Version 1.0
-- You could alter to one big string .e.e numebrs , alpha special etc
-- added for more felxibility in case I want to extend i.e put logic  in for 3 numbers, 2 pecials 3 numbers etc
-- for now just randomly pick one of them


DECLARE @SWAP                   VARCHAR(8000);      -- Will be used as a tempoary buffer
DECLARE @SELECTOR               INTEGER = 0;


DECLARE @CURRENTLENGHT          INTEGER = 0;
WHILE @CURRENTLENGHT < @DESIREDLENGTH
BEGIN


-- Do we want a number, special character or Alphabet Randonly decide?
SET @SELECTOR  = CAST(ABS(CHECKSUM(NEWID())) % 3 AS INTEGER);   -- Always three 1 number , 2 alphaBET , 3 special;
IF @SELECTOR = 0
BEGIN
SET @SELECTOR = 3
END;


-- SET SWAP VARIABLE AS DESIRED
SELECT @SWAP = CASE WHEN @SELECTOR = 1 THEN @NUMBERS WHEN @SELECTOR = 2 THEN @ALPHABET ELSE @SPECIALS END;


-- MAKE THE SELECTION
SET @SELECTOR  = CAST(ABS(CHECKSUM(NEWID())) % LEN(@SWAP) AS INTEGER);
IF @SELECTOR = 0
BEGIN
SET @SELECTOR = LEN(@SWAP)
END;


SET @RANDOMSTRING = ISNULL(@RANDOMSTRING,'') + SUBSTRING(@SWAP,@SELECTOR,1);
SET @CURRENTLENGHT = LEN(@RANDOMSTRING);
END;


END;


GO


DECLARE @RANDOMSTRING VARCHAR(8000)


EXEC GenerateARandomString @RANDOMSTRING = @RANDOMSTRING OUT


SELECT @RANDOMSTRING

小开

有时候我们需要很多随机的东西: 爱、善良、假期等等。这些年来，我随机收集了一些发电机，这些是来自 Pinal Dave 的，还有我曾经找到的一个堆栈溢出问题的答案。下面是裁判。

--Adapted from Pinal Dave; http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/
SELECT
ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1 AS RandomInt
, CAST( (ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1)/7.0123 AS NUMERIC( 15,4)) AS RandomNumeric
, DATEADD( DAY, -1*(ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1), GETDATE()) AS RandomDate
--This line from http://stackoverflow.com/questions/15038311/sql-password-generator-8-characters-upper-and-lower-and-include-a-number
, CAST((ABS(CHECKSUM(NEWID()))%10) AS VARCHAR(1)) + CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) + CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) + LEFT(NEWID(),5) AS RandomChar
, ABS(CHECKSUM(NEWID()))%50000+1 AS RandomID

小开

在 SQL Server 2012+中，我们可以连接一些(G) UID 的二进制文件，然后对结果进行基地64转换。

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
;

如果需要更长的字符串(或者在结果中看到 =字符) ，则需要在子选择中添加更多的 + CAST(newid() as varbinary(max))。

小开

所以我喜欢上面的许多答案，但是我在寻找一些自然界中更随机的东西。我还想要一种显式调用被排除字符的方法。下面是我的解决方案，它使用一个视图调用 CRYPT_GEN_RANDOM来获得一个加密随机数。在我的示例中，我只选择了一个8字节的随机数。请注意，您可以增加这个大小，如果需要，还可以利用函数的种子参数。下面是文档的链接: https://learn.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];

创建视图的原因是不能从函数直接调用 CRYPT_GEN_RANDOM。

从那里，我创建了一个标量函数，它接受一个长度和一个字符串参数，它可以包含一个逗号分隔的排除字符字符串。

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

下面是如何调用该函数的示例。

SELECT [dbo].[fn_GenerateRandomString](8,'!,@,#,$,%,&,?');

小开

对于一个随机的字母，你可以使用:

select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
(abs(checksum(newid())) % 26)+1, 1)

使用 newid()与使用 rand()之间的一个重要区别是，如果返回多行，则对每一行分别计算 newid()，而对整个查询计算一次 rand()。

小开

这是我今天想出来的一个答案(因为我不喜欢现有的任何一个答案)。

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.

下面是源代码(希望注释有所帮助) :

/**
* 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

它不是一个存储过程，但是将它转换成一个存储过程并不难。它的速度也不慢得可怕(生成1000个长度为60的结果花费了我大约0.3秒的时间，这比我个人所需要的时间还要多) ，这是我最初对我正在做的所有字符串变异的担忧之一。

这里的主要结论是，我不会尝试创建自己的随机数生成器，而且我的角色集是不受限制的。我只是简单地使用 SQL 所拥有的随机生成器(我知道有 rand()，但这对于表结果来说不是很好)。希望这种方法结合了这两种答案，从过于简单(即只有 newid())和过于复杂(即自定义随机数算法)。

它也很简短(减去评论) ，而且容易理解(至少对我来说) ，这在我的书中总是一个优点。

然而，这个方法不能被种子化，所以它每次都是真正随机的，并且您不能用任何可靠的方法来复制相同的数据集。OP 并没有把它列为一个需求，但是我知道有些人在寻找这样的东西。

我知道我来晚了，但希望有人会发现这个有用。

小开

很简单的，用它享受吧。

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;

小开

这将从 Base64范围(上限、下限、数字、 + 和/)生成一个长度为96个字符的字符串。添加3“ NEWID ()”将使长度增加32，而且没有 Base64填充(=)。

    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

如果你要把它应用到一个集合中，一定要从这个集合中引入一些东西，以便重新计算 NEWID () ，否则每次都会得到相同的值:

  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

小开

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))

注意，字节长度与编码后的大小不一样; 请使用这个文章中的以下内容进行转换:

Bytes = 3 * (LengthInCharacters / 4) - Padding

小开

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)

小开

下面是在 SQL 中生成4或8个字符长的随机字母数字字符串的方法

select LEFT(CONVERT(VARCHAR(36),NEWID()),4)+RIGHT(CONVERT(VARCHAR(36),NEWID()),4)

 SELECT RIGHT(REPLACE(CONVERT(VARCHAR(36),NEWID()),'-',''),8)

小开

创建或更改 PROC USP _ GENERATE _ RANDOM _ CHARACTER (@NO _ OF _ CHARS INT,@RANDOM _ CHAR VARCHAR (40) OUTPUT) AS 开始

SELECT @RANDOM_CHAR  = SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS)

/* 用法: 声明@OUT VARCHAR (40) EXEC USP _ GENERATE _ RANDOM _ CHARACTER 13,@RANDOM _ CHAR =@OUT OUTPUT SELECT@OUT */

小开

小修改 Remus Rusanu 代码-感谢分享

这将生成一个随机字符串，并且可以在没有种子值的情况下使用

   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

小开

Another simple solution with the complete alphabet:

SELECT LEFT(REPLACE(REPLACE((SELECT CRYPT_GEN_RANDOM(16) FOR XML PATH(''), BINARY BASE64),'+',''),'/',''),16);

用所需的长度替换两个16。

样本结果:

pzyMATe3jJwN1XkB

小开

希望这将是一个创建 random lower & upper字符的答案。它使用 while 循环，可以控制它生成具有特定长度的字符串。

--random string generator
declare @maxLength int = 5; --max length
declare @bigstr varchar(10) --uppercase character
declare @smallstr varchar(10) --lower character
declare @i int = 1;
While @i <= @maxLength
begin
set @bigstr = concat(@bigstr, char((rand()*26 + 65)));
set @smallstr = concat(@smallstr, char((rand()*26 + 96)));
set @i = len(@bigstr)
end


--select query
select @bigstr, @smallstr