使用T-SQL生成随机字符串 [英] Generating random strings with T-SQL

问题描述

如果您想使用T-SQL生成伪随机的字母数字字符串，您将如何做?您如何从其中排除美元符号，破折号和斜线之类的字符?

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?

推荐答案

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

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;  

更新2016年2月17日:参见下面的评论，原始过程在推进随机种子的方式方面存在问题.我更新了代码，并修复了上述问题.

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.

这篇关于使用T-SQL生成随机字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！