使用 T-SQL 生成随机字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1324063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generating random strings with T-SQL
提问by Scott Lawrence
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?
如果您想使用 T-SQL 生成伪随机字母数字字符串，您会怎么做？您将如何从中排除美元符号、破折号和斜线等字符？
采纳答案by Remus Rusanu
When generating random data, specially for test, it is very useful to make the data random, but reproducible. The secret is to use explicit seeds for the random function, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a function that generates object names in a reproducible manner:
在生成随机数据时，特别是测试时，让数据随机化，但可重现是非常有用的。秘诀是为随机函数使用显式种子，这样当使用相同的种子再次运行测试时，它会再次产生完全相同的字符串。下面是一个以可重现方式生成对象名称的函数的简化示例：
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
When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:
运行测试时，调用者生成一个随机种子，它与测试运行相关联（将其保存在结果表中），然后传递种子，类似于：
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;
Update 2016-02-17: See the comments bellow, the original procedure had an issue in the way it advanced the random seed. I updated the code, and also fixed the mentioned off-by-one issue.
2016 年 2 月 17 日更新：请参阅下面的评论，原始程序在推进随机种子的方式上存在问题。我更新了代码，还修复了提到的一对一问题。
回答by Stefan Steinegger
Using a guid
使用指南
SELECT @randomString = CONVERT(varchar(255), NEWID())
very short ...
很短 ...
回答by Chris Judge
Similar to the first example, but with more flexibility:
类似于第一个示例，但具有更大的灵活性：
-- 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)
SELECT @LoopCount = @LoopCount + 1
END
I forgot to mention one of the other features that makes this more flexible. By repeating blocks of characters in @CharPool, you can increase the weighting on certain characters so that they are more likely to be chosen.
我忘了提及使这更灵活的其他功能之一。通过在@CharPool 中重复字符块，您可以增加某些字符的权重，使它们更有可能被选中。
回答by Escarcha
Use the following code to return a short string:
使用以下代码返回一个短字符串：
SELECT SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)
回答by Kevin O
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.
如果您运行的是 SQL Server 2008 或更高版本，则可以使用新的加密函数 crypt_gen_random()，然后使用 base64 编码使其成为字符串。这最多适用于 8000 个字符。
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
回答by Henrique
I'm not expert in T-SQL, but the simpliest way I've already used it's like that:
我不是 T-SQL 专家，但我已经使用过的最简单的方法是这样的：
select char((rand()*25 + 65))+char((rand()*25 + 65))
This generates two char (A-Z, in ascii 65-90).
这会生成两个字符（AZ，ascii 65-90）。
回答by Hammad Khan
select left(NEWID(),5)
This will return the 5 left most characters of the guid string
这将返回 guid 字符串最左边的 5 个字符
Example run
------------
11C89
9DB02
回答by Krishna Thota
Here is a random alpha numeric generator
这是一个随机字母数字生成器
print left(replace(newid(),'-',''),@length) //--@length is the length of random Num.
回答by krubo
For one random letter, you can use:
对于一个随机字母，您可以使用：
select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
(abs(checksum(newid())) % 26)+1, 1)
An important difference between using newid()versus rand()is that if you return multiple rows, newid()is calculated separately for each row, while rand()is calculated once for the whole query.
usingnewid()和using 之间的一个重要区别rand()是，如果返回多行，newid()则为每一行单独计算，而rand()为整个查询计算一次。
回答by Brian
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.
这对我有用：我只需要为 ID 生成三个随机的字母数字字符，但它可以适用于最多 15 个左右的任何长度。
declare @DesiredLength as int = 3;
select substring(replace(newID(),'-',''),cast(RAND()*(31-@DesiredLength) as int),@DesiredLength);
