blocks|key|1255509|text|我首先遇到了this+blog+post，然后为此提出了以下存储过程，我正在当前项目中使用该存储过程(对于奇怪的格式，很抱歉)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1255510|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+%2B+CONVERT(INT,+(122-48%2B1)*RAND()))

IF+ASCII(@nextChar)+not+in+(58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT+@randomString+=+@randomString+%2B+@nextChar
SET+@counter+=+@counter+%2B+1
END
END
END|code-block|syntax|javascript|1255511|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/^0|6|E|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I came across <a href="http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/" rel="nofollow noreferrer">this blog post</a> first, then came up with the following stored procedure for this that I'm using on a current project (sorry for the weird formatting):

<pre><code>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 &lt;= @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
</code></pre>

blocks|key|1470240|text|在SQL2000中，我创建了一个包含我想要使用的字符的表，创建了一个按newid()从该表中选择字符的视图，然后从该视图中选择了前1个字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470241|CREATE+VIEW+dbo.vwCodeCharRandom
AS
SELECT+TOP+100+PERCENT+
++++CodeChar
FROM+dbo.tblCharacter
ORDER+BY+
++++NEWID()

...

SELECT+TOP+1+CodeChar+FROM+dbo.vwCodeCharRandom|code-block|syntax|javascript|1470242|然后，您可以简单地从视图中提取字符，并根据需要将它们连接起来。|1470243|编辑:受到斯蒂芬的回应的启发...|1470244|select+top+1+RandomChar+from+tblRandomCharacters+order+by+newid()|1470245|不需要视图(事实上，我不确定为什么要这样做--代码来自几年前)。您仍然可以指定要在表格中使用的字符。|1470246|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I did this in SQL 2000 by creating a table that had characters I wanted to use, creating a view that selects characters from that table ordering by newid(), and then selecting the top 1 character from that view.

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

...

SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom
</code></pre>

Then you can simply pull characters from the view and concatenate them as needed.

EDIT: Inspired by Stephan's response...

<pre><code>select top 1 RandomChar from tblRandomCharacters order by newid()
</code></pre>

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.

blocks|key|1255637|text|使用guid|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1255638|SELECT+@randomString+=+CONVERT(varchar(255),+NEWID())|code-block|syntax|javascript|1255639|非常简短..。|1255640|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Using a guid

<pre><code>SELECT @randomString = CONVERT(varchar(255), NEWID())
</code></pre>

very short ...

blocks|key|1470367|text|与第一个示例类似，但更灵活：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470368|--+min_length+=+8,+max_length+=+12
SET+@Length+=+RAND()+*+5+%2B+8
--+SET+@Length+=+RAND()+*+(max_length+-+min_length+%2B+1)+%2B+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.,-_!$@#%25%5E&*'
SET+@PoolLength+=+Len(@CharPool)

SET+@LoopCount+=+0
SET+@RandomString+=+''

WHILE+(@LoopCount+<+@Length)+BEGIN
++++SELECT+@RandomString+=+@RandomString+%2B+
++++++++SUBSTRING(@Charpool,+CONVERT(int,+RAND()+*+@PoolLength)+%2B+1,+1)
++++SELECT+@LoopCount+=+@LoopCount+%2B+1
END|code-block|syntax|javascript|1470369|我忘了提到让这个功能更灵活的另一个特性。通过重复@CharPool中的字符块，您可以增加某些字符的权重，以便它们更有可能被选中。|1470370|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Similar to the first example, but with more flexibility:
<pre><code>-- 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.,-_!$@#%^&amp;*'
SET @PoolLength = Len(@CharPool)

SET @LoopCount = 0
SET @RandomString = ''

WHILE (@LoopCount &lt; @Length) BEGIN
 SELECT @RandomString = @RandomString + 
 SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1)
 SELECT @LoopCount = @LoopCount + 1
END
</code></pre>
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.

blocks|key|1255781|text|在生成随机数据时，特别是为了测试，使数据随机但可重现是非常有用的。秘诀是对随机函数使用显式的种子，这样当使用相同的种子再次运行测试时，它会再次生成完全相同的字符串。以下是以可重现的方式生成对象名称的函数的简化示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1255782|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+%2B+'_@';

++++set++@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);

++++select+@length+=+@minLen+%2B+rand(@seed)+*+(@maxLen-@minLen)
++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);

++++declare+@dice+int;
++++select+@dice+=+rand(@seed)+*+len(@first),
++++++++@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++select+@string+=+substring(@first,+@dice,+1);

++++while+0+<+@length+
++++begin
++++++++select+@dice+=+rand(@seed)+*+100
++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++++++if+(@dice+<+10)+--+10%25+special+chars
++++++++begin
++++++++++++select+@dice+=+rand(@seed)+*+len(@specials)%2B1
++++++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++++++++++select+@string+=+@string+%2B+substring(@specials,+@dice,+1);
++++++++end
++++++++else+if+(@dice+<+10%2B10)+--+10%25+digits
++++++++begin
++++++++++++select+@dice+=+rand(@seed)+*+len(@digit)%2B1
++++++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++++++++++select+@string+=+@string+%2B+substring(@digit,+@dice,+1);
++++++++end
++++++++else+--+rest+80%25+alpha
++++++++begin
++++++++++++declare+@preseed+int+=+@seed;
++++++++++++select+@dice+=+rand(@seed)+*+len(@alpha)%2B1
++++++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);

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

++++++++select+@length+=+@length+-+1;+++
++++end
end
go|code-block|syntax|javascript|1255783|在运行测试时，调用者生成一个与测试运行关联的随机种子(将其保存在结果表中)，然后传递种子，如下所示：|1255784|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;++|1255785|更新2016-02-17:查看下面的评论，原始过程在推进随机种子的方式上存在问题。我更新了代码，还修复了前面提到的off-by-one问题。|1255786|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>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 &lt; @length 
 begin
 select @dice = rand(@seed) * 100
 , @seed = (rand((@seed+@step)%2147483647)*2147483647);
 if (@dice &lt; 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 &lt; 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
</code></pre>

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:

<pre><code>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; 
</code></pre>

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.

blocks|key|1255831|text|使用以下代码返回一个短字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1255832|SELECT+SUBSTRING(CONVERT(varchar(40),+NEWID()),0,9)|code-block|syntax|javascript|1255833|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the following code to return a short string:

<pre><code>SELECT SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)
</code></pre>

blocks|key|1470519|text|这是一个随机的字母数字生成器|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470520|print+left(replace(newid(),'-',''),@length)+//--@length+is+the+length+of+random+Num.|code-block|syntax|javascript|1470521|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a random alpha numeric generator

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

blocks|key|1470566|text|这是一些基于新Id的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470567|with+list+as+
(
++++select+1+as+id,newid()+as+val
+++++++++union+all
++++select+id+%2B+1,NEWID()
++++from++++list+++
++++where+++id+%2B+1+<+10
)+
select+ID,val+from+list
option+(maxrecursion+0)|code-block|syntax|javascript|1470568|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Heres something based on New Id. 

<pre><code>with list as 
(
 select 1 as id,newid() as val
 union all
 select id + 1,NEWID()
 from list 
 where id + 1 &lt; 10
) 
select ID,val from list
option (maxrecursion 0)
</code></pre>

blocks|key|1255972|text|这对我来说很有效:我只需要为一个ID生成三个随机的字母数字字符，但它可以工作到15个左右的任何长度。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1255973|declare+@DesiredLength+as+int+=+3;
select+substring(replace(newID(),'-',''),cast(RAND()*(31-@DesiredLength)+as+int),@DesiredLength);|code-block|syntax|javascript|1255974|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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. 

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

blocks|key|1256025|text|我想我应该分享，或者回馈社区。它是基于ASCII的，解决方案并不完美，但它工作得很好。好好享受，Goran+B。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256026|/*+
--+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%2B1)+BEGIN
++++SELECT+@maskedString+=+@maskedString+%2B+
++++ISNULL(
++++++++CASE+
++++++++WHEN+ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound+AND+ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
+++++++++THEN
++++++++++++CHAR(ASCII(SUBSTRING(@inpString,@position,1))%2B
++++++++++++(case+when+@ipOffset+is+null+then
++++++++++++case+when+ASCII(SUBSTRING(@inpString,@position,1))%252=0+then+@offsetAsciiUp1+else+@offsetAsciiDown1+end
++++++++++++else+@ipOffset+end))
++++++++WHEN+ASCII(SUBSTRING(@inpString,@position,1))<=@asciiLoBound
+++++++++THEN+'('%2BCONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))%2B1000)%2B')'+-->+wrap+for+decode
++++++++WHEN+ASCII(SUBSTRING(@inpString,@position,1))>=@asciiHiBound
+++++++++THEN+'('%2BCONVERT(varchar,ASCII(SUBSTRING(@inpString,@position,1))%2B1000)%2B')'+-->+wrap+for+decode
++++++++END
++++++++,'')
++++SELECT+@position+=+@position+%2B+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%2B1)+BEGIN
++++SELECT+@maskedString+=+@maskedString+%2B+
++++ISNULL(
++++++++CASE+
++++++++WHEN+ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound+AND+ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
+++++++++THEN
++++++++++++CHAR(ASCII(SUBSTRING(@inpString,@position,1))%2B
++++++++++++(case+when+@ipOffset+is+null+then
++++++++++++case+when+ASCII(SUBSTRING(@inpString,@position,1))%252=1+then+@offsetAsciiDown1+else+@offsetAsciiUp1+end
++++++++++++else+@ipOffset*(-1)+end))
++++++++ELSE+''
++++++++END
++++++++,'')
++++SELECT+@position+=+@position+%2B+1
END

select+@maskedString|code-block|syntax|javascript|1256027|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I thought I'd share, or give back to the community ... 
It's ASCII based, and the solution is not perfect but it works quite well.
Enjoy,
Goran B.

<pre><code>/* 
-- 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 --&gt; up to and NOT including
set @asciiLoBound=31 --&gt; up from and NOT including

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

SET @position = 1
SET @maskedString = ''

--&gt; MASK:
---------
WHILE (@position &lt; @length+1) BEGIN
 SELECT @maskedString = @maskedString + 
 ISNULL(
 CASE 
 WHEN ASCII(SUBSTRING(@inpString,@position,1))&gt;@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))&lt;@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))&lt;=@asciiLoBound
 THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))+1000)+')' --&gt; wrap for decode
 WHEN ASCII(SUBSTRING(@inpString,@position,1))&gt;=@asciiHiBound
 THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@inpString,@position,1))+1000)+')' --&gt; wrap for decode
 END
 ,'')
 SELECT @position = @position + 1
END

select @MaskedString


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

SET @position = 1
SET @maskedString = ''

--&gt; UNMASK (Limited to within ascii lo-hi bound):
-------------------------------------------------
WHILE (@position &lt; @length+1) BEGIN
 SELECT @maskedString = @maskedString + 
 ISNULL(
 CASE 
 WHEN ASCII(SUBSTRING(@inpString,@position,1))&gt;@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))&lt;@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
</code></pre>

blocks|key|1256086|text|如果您运行的是SQL+Server2008或更高版本，则可以使用新的加密函数crypt_gen_random()，然后使用base64编码将其转换为字符串。这将适用于最多8000个字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256087|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|code-block|syntax|javascript|1256088|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

blocks|key|1256119|text|select+left(NEWID(),5)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1256120|这将返回guid字符串最左侧的5个字符|unstyled|1256121|Example+run
------------
11C89
9DB02|1256122|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>select left(NEWID(),5)
</code></pre>

This will return the 5 left most characters of the guid string

<pre><code>Example run
------------
11C89
9DB02
</code></pre>

blocks|key|1470845|text|我意识到这是一个古老的问题，有很多很好的答案。然而，当我发现这篇文章时，我还发现了Saeid+Hasani在TechNet上发表的一篇更新的文章|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470846|T-SQL:+How+to+Generate+Random+Passwords|offset|length|1470847|虽然解决方案侧重于密码，但它适用于一般情况。Saeid通过各种考虑得出解决方案。这是非常有启发性的。|1470848|包含本文中所有代码块的脚本可以通过TechNet+Gallery单独获得，但我一定要从本文开始。|1470849|entityMap|0|LINK|mutability|MUTABLE|url|http://social.technet.microsoft.com/wiki/contents/articles/25121.t-sql-how-to-generate-random-passwords.aspx|1|https://social.technet.microsoft.com/Search/en-US?query=tsql%2520how%2520to%2520generate%2520random%2520passwords^0|0|0|13|0|0|0|H|F|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|J|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]|R|$5|M|N|O|A|$P|S]]]]

I realize that this is an old question with many fine answers. However when I found this I also found a more recent article on TechNet by Saeid Hasani

<a href="http://social.technet.microsoft.com/wiki/contents/articles/25121.t-sql-how-to-generate-random-passwords.aspx" rel="nofollow">T-SQL: How to Generate Random Passwords</a>

While the solution focuses on passwords it applies to the general case. Saeid works through various considerations to arrive at a solution. It is very instructive.

A script containing all the code blocks form the article is separately available via the <a href="https://social.technet.microsoft.com/Search/en-US?query=tsql%20how%20to%20generate%20random%20passwords" rel="nofollow">TechNet Gallery</a>, but I would definitely start at the article.

blocks|key|1470884|text|与其他答案一样，它使用带有种子的rand，但没有必要在每次调用时都提供种子。在第一次调用时提供它就足够了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470885|这是我修改过的代码。|1470886|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+%2B+'_@';

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

++++select+@length+=+@minLen+%2B+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%25+special+chars
++++++++begin
++++++++++++select+@dice+=+rand()+*+len(@specials)%2B1;
++++++++++++select+@string+=+@string+%2B+substring(@specials,+@dice,+1);
++++++++end
++++++++else+if+(@dice+<+10%2B10)+--+10%25+digits
++++++++begin
++++++++++++select+@dice+=+rand()+*+len(@digit)%2B1;
++++++++++++select+@string+=+@string+%2B+substring(@digit,+@dice,+1);
++++++++end
++++++++else+--+rest+80%25+alpha
++++++++begin
++++++++++++select+@dice+=+rand()+*+len(@alpha)%2B1;

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

++++++++select+@length+=+@length+-+1;+++
++++end
end
go|code-block|syntax|javascript|1470887|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This uses rand with a seed like one of the other answers, but it is not necessary to provide a seed on every call. Providing it on the first call is sufficient.

This is my modified code.

<pre><code>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 = '_@#$&amp;'
 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 &lt; @length 
 begin
 select @dice = rand() * 100;
 if (@dice &lt; 10) -- 10% special chars
 begin
 select @dice = rand() * len(@specials)+1;
 select @string = @string + substring(@specials, @dice, 1);
 end
 else if (@dice &lt; 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
</code></pre>

blocks|key|1470930|text|我不是T-SQL专家，但我已经使用过的最简单的方法是这样的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470931|select+char((rand()*25+%2B+65))%2Bchar((rand()*25+%2B+65))|code-block|syntax|javascript|1470932|这会生成两个字符(+ascii+65-90中的A-Z)。|1470933|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>select char((rand()*25 + 65))+char((rand()*25 + 65))
</code></pre>

This generates two char (A-Z, in ascii 65-90).

blocks|key|1256302|text|有很多好的答案，但到目前为止，它们都不允许可定制的字符池，并作为列的默认值。我希望能够做这样的事情：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256303|alter+table+MY_TABLE+add+MY_COLUMN+char(20)+not+null
++default+dbo.GenerateToken(crypt_gen_random(20))|code-block|syntax|javascript|1256304|所以我想出了这个。如果您修改了硬编码的数字32，请注意它。|1256305|--+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+%2B+1;
++++select+@oneByte+=+convert(tinyint,+substring(@randomBytes,+@index,+1));
++++select+@oneChar+=+substring(@allowedChars,+1+%2B+(@oneByte+%25+32),+1);+--+32+is+the+number+of+@allowedChars
++++select+@token+=+@token+%2B+@oneChar;
end

return+@token;

end|1256306|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

There are a lot of good answers but so far none of them allow a customizable character pool and work as a default value for a column. I wanted to be able to do something like this:

<pre><code>alter table MY_TABLE add MY_COLUMN char(20) not null
 default dbo.GenerateToken(crypt_gen_random(20))
</code></pre>

So I came up with this. Beware of the hard-coded number 32 if you modify it.

<pre><code>-- 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 &lt; 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
</code></pre>

blocks|key|1256369|text|我使用我开发的这个程序简单地插入你想要的字符，以便能够在输入变量中显示，你也可以定义长度。希望这个格式很好，我是个堆栈溢出的新手。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256370|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)+
++++++++=+'_=%2B-$£%25%5E&*()"!@~#:',+
+++++@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()))+%25+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()))+%25+LEN(@SWAP)+AS+INTEGER);
++++++++IF+@SELECTOR+=+0
++++++++BEGIN
++++++++++++SET+@SELECTOR+=+LEN(@SWAP)
++++++++END;

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

END;

GO

DECLARE+@RANDOMSTRING+VARCHAR(8000)

EXEC+GenerateARandomString+@RANDOMSTRING+=+@RANDOMSTRING+OUT

SELECT+@RANDOMSTRING|code-block|syntax|javascript|1256371|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I use this procedure that I developed simply stipluate the charaters you want to be able to display in the input variables, you can define the length too.
Hope this formats well, I am new to stack overflow.

<pre><code>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) 
 = '_=+-$£%^&amp;*()"!@~#:', 
 @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 &lt; @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
</code></pre>

blocks|key|1471156|text|有时我们需要很多随机的东西:爱，友善，度假，等等。这些年来我收集了一些随机生成器，这些生成器来自Pinal+Dave和我曾经找到的stackoverflow答案。参考文献如下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471157|--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))+%251000)+%2B+1+AS+RandomInt
++++,+CAST(+(ABS(+CAST(+NEWID()+AS+BINARY(+6))+%251000)+%2B+1)/7.0123+AS+NUMERIC(+15,4))+AS+RandomNumeric
++++,+DATEADD(+DAY,+-1*(ABS(+CAST(+NEWID()+AS+BINARY(+6))+%251000)+%2B+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()))%2510)+AS+VARCHAR(1))+%2B+CHAR(ASCII('a')%2B(ABS(CHECKSUM(NEWID()))%2525))+%2B+CHAR(ASCII('A')%2B(ABS(CHECKSUM(NEWID()))%2525))+%2B+LEFT(NEWID(),5)+AS+RandomChar
++++,+ABS(CHECKSUM(NEWID()))%2550000%2B1+AS+RandomID|code-block|syntax|javascript|1471158|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Sometimes we need a lot of random things: love, kindness, vacation, etc. 
I have collected a few random generators over the years, and these are from Pinal Dave and a stackoverflow answer I found once. Refs below.

<pre><code>--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
</code></pre>

blocks|key|1256522|text|所以我喜欢上面的许多答案，但我在寻找一些本质上更随机的东西。我还想要一种显式调用排除字符的方法。下面是我的解决方案，它使用一个视图调用CRYPT_GEN_RANDOM来获取加密随机数。在我的例子中，我只选择了一个8字节的随机数。请注意，您可以增加此大小，如果需要，还可以使用函数的种子参数。以下是文档的链接：https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1256523|CREATE+VIEW+[dbo].[VW_CRYPT_GEN_RANDOM_8]
AS
SELECT+CRYPT_GEN_RANDOM(8)+as+[value];|code-block|syntax|javascript|1256524|创建视图的原因是不能直接从函数调用CRYPT_GEN_RANDOM。|1256525|在此基础上，我创建了一个标量函数，该函数接受一个长度和一个字符串参数，该参数可以包含一个逗号分隔的排除字符字符串。|1256526|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,',%5E,*,(,),-,_,=,%2B,[,{,],},\,%7C,;,:,'',",<,.,>,/,`,~');
++++
++++--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]))+%25+94)+%2B+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+%2B+CHAR(@asciiValue);
++++++++END
++++END

++++RETURN(@returnValue);
END|1256527|下面是一个如何调用该函数的示例。|1256528|SELECT+[dbo].[fn_GenerateRandomString](8,'!,@,#,$,%25,&,?');|1256529|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql^0|1V|G|4A|2A|0|0|0|H|G|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|F|3|G|5|H|7|18|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1C|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1E|8|@]|D|@]|E|$]]|$1|S|3|T|5|H|7|1F|8|@]|D|@]|E|$I|J]]|$1|U|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

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 <code>CRYPT_GEN_RANDOM</code> 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: <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql</a>
<pre><code>CREATE VIEW [dbo].[VW_CRYPT_GEN_RANDOM_8]
AS
SELECT CRYPT_GEN_RANDOM(8) as [value];
</code></pre>
The reason for creating the view is because <code>CRYPT_GEN_RANDOM</code> 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.
<pre><code>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,',^,*,(,),-,_,=,+,[,{,],},\,|,;,:,'',&quot;,&lt;,.,&gt;,/,`,~');
 
 --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) &lt; @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
</code></pre>
Below is an example of the how to call the function.
<pre><code>SELECT [dbo].[fn_GenerateRandomString](8,'!,@,#,$,%,&amp;,?');
</code></pre>

blocks|key|1256595|text|对于一个随机字母，您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256596|select+substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
+++++++++++++++++(abs(checksum(newid()))+%25+26)%2B1,+1)|code-block|syntax|javascript|1256597|使用newid()与使用rand()之间的一个重要区别是，如果返回多个行，则分别计算每行的newid()，而计算整个查询的rand()一次。|offset|length|style|CODE|1256598|entityMap^0|0|0|2|7|C|6|19|7|1P|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|N|$]]

For one random letter, you can use:

<pre><code>select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 (abs(checksum(newid())) % 26)+1, 1)
</code></pre>

An important difference between using <code>newid()</code> versus <code>rand()</code> is that if you return multiple rows, <code>newid()</code> is calculated separately for each row, while <code>rand()</code> is calculated once for the whole query.

blocks|key|1256700|text|这是我今天想出的一个答案(因为我不太喜欢现有的答案)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256701|它生成一个随机字符串的临时表，基于newid()，但也支持自定义字符集(因此不只是0-9+&+A-F)、自定义长度(最大255，限制是硬编码的，但可以更改)和自定义随机记录数量。|offset|length|style|CODE|1256702|以下是源代码(希望评论能提供帮助)：|1256703|/**
+*+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+%2B+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+%2B+1

++++--+Append+to+the+original+value,+limit+@length+*+2
++++update+@random
++++++++set+value+=+substring(value+%2B+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+%2B+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)+%2B

++++++++++++/**
+++++++++++++*+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+%2B+1,+1)+%2B
++++--++(1)+-------------------------------------------------------%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E-----------------------------------------
++++--++(2)+---------------------------------%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E11111111111111111111111%5E%5E%5E%5E-------------------------------------
++++--++(3)+--------------------%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E2222222222222222222222222222222222222222222222222%5E------------------------------------
++++--++(4)+--------------------333333333333333333333333333333333333333333333333333333333333333---%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E--------
++++--++(5)+--------------------333333333333333333333333333333333333333333333333333333333333333%5E%5E%5E4444444444444444444444444--------
++++--++(6)+--------------------5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555%5E%5E%5E%5E----
++++--++(7)+%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E%5E66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666%5E%5E%5E%5E

++++++++++++/**
+++++++++++++*+(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+%2B+@i,+len(value))

end

--+Select+the+results
select+value
from+@random|code-block|syntax|javascript|1256704|它不是存储过程，但将其转换为存储过程并不困难。它也不会慢得可怕(我花了大约0.3秒来生成长度为60的1000个结果，这比我个人需要的还要多)，这是我对我正在进行的所有字符串突变的最初担忧之一。|1256705|这里的主要收获是，我不想创建自己的随机数生成器，而且我的字符集也不受限制。我只是简单地使用SQL的随机生成器(我知道有rand()，但这对于表结果来说不是很好)。希望这种方法将这里的两种答案结合在一起，从过于简单(即newid())和过于复杂(即自定义随机数算法)。|1256706|它也很简短(没有注释)，易于理解(至少对我来说)，这在我的书中一直是一个加分。|1256707|然而，这种方法不能被播种，所以它每次都是真正随机的，你将无法以任何可靠的方式复制相同的数据集。操作员并没有将此列为要求，但我知道有些人在寻找这类东西。|1256708|我知道我来晚了，但希望有人会觉得这很有用。|1256709|entityMap^0|0|H|7|0|0|0|0|1N|6|30|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|15|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|17|8|@$D|18|E|19|F|G]|$D|1A|E|1B|F|G]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1D|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|Z|$]]

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 <code>newid()</code>, but also supports a custom character set (so more than just 0-9 &amp; 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):

<pre><code>/**
 * 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 &lt; @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 &lt; @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 &lt; @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
</code></pre>

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 <code>rand()</code>, but that's not great for table results). Hopefully this approach marries the two kinds of answers here, from overly simple (i.e. just <code>newid()</code>) 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.

blocks|key|1256772|text|这是非常simply.use它和享受。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256773|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'+%2B+--lower+letters
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'+%2B+--upper+letters
'1234567890';+--number+characters

SELECT+@result+=
(
++++SELECT+TOP+(@length)
+++++++++++SUBSTRING(@String,+1+%2B+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;|code-block|syntax|javascript|1256774|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

it's very simply.use it and enjoy.

<pre><code>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 &lt; DATALENGTH(@String)
 AND type = 'P'
 ORDER BY
(
 SELECT TOP 1 Id FROM dbo.vwGetNewId
) --instead of using newid()
 FOR XML PATH('')
);

RETURN @result;

END;
</code></pre>

blocks|key|1471495|text|这将从Base64范围(大写、小写、数字、%2B和/)中产生一个长度为96个字符的字符串。添加3个"NEWID()“将使长度增加32，没有Base64填充(=)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471496|++++SELECT+
++++++++CAST(
++++++++++++CONVERT(NVARCHAR(MAX),
++++++++++++++++CONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++,2)+
++++++++AS+XML).value('xs:base64Binary(xs:hexBinary(.))',+'VARCHAR(MAX)')+AS+StringValue|code-block|syntax|javascript|1471497|如果要将此方法应用于某个集合，请确保引入该集合中的某些内容，以便重新计算NEWID()，否则每次都会得到相同的值：|1471498|++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())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+NEWID())
++++++++++++++++++++%2BCONVERT(VARBINARY(8),+U.UserID)++--+Causes+a+recomute+of+all+NEWID()+calls
++++++++++++++++,2)+
++++++++++++AS+XML).value('xs:base64Binary(xs:hexBinary(.))',+'VARCHAR(MAX)')+AS+StringValue
++++)+PseudoRandom|1471499|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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 (=).

<pre><code> 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
</code></pre>

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:

<pre><code> 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
</code></pre>

blocks|key|1256914|text|对于SQL+Server+2016和更高版本，以下是一个非常简单且相对有效的表达式，用于生成给定字节长度的加密随机字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1256915|--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))|code-block|syntax|javascript|1256916|请注意，字节长度与编码大小不同；请使用this文章中的以下内容进行转换：|offset|length|1256917|Bytes+=+3+*+(LengthInCharacters+/+4)+-+Padding|1256918|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.aaronlenoir.com/2017/11/10/get-original-length-from-base-64-string/^0|0|0|J|4|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

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:

<pre><code>--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))
</code></pre>

Note that the byte length is not the same as the encoded size; use the following from <a href="https://blog.aaronlenoir.com/2017/11/10/get-original-length-from-base-64-string/" rel="nofollow noreferrer">this</a> article to convert:

<pre><code>Bytes = 3 * (LengthInCharacters / 4) - Padding
</code></pre>

blocks|key|1471624|text|根据本文中各种有用的回复，我找到了我喜欢的几个选项的组合。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471625|DECLARE+@UserId+BIGINT+=+12345+--+a+uniqueId+in+my+system
SELECT+LOWER(REPLACE(NEWID(),'-',''))+%2B+CONVERT(VARCHAR,+@UserId)|code-block|syntax|javascript|1471626|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Based on various helpful responses in this article I landed with a combination of a couple options I liked.

<pre><code>DECLARE @UserId BIGINT = 12345 -- a uniqueId in my system
SELECT LOWER(REPLACE(NEWID(),'-','')) + CONVERT(VARCHAR, @UserId)
</code></pre>

blocks|key|1257088|text|CREATE+OR+ALTER+PROC+OUTPUT(+@NO_OF_CHARS+INT，@RANDOM_CHAR+VARCHAR(40)+OUTPUT)+AS+BEGIN|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1257089|SELECT+@RANDOM_CHAR++=+SUBSTRING+(REPLACE(CONVERT(VARCHAR(40),+NEWID()),+'-',''),+1,+@NO_OF_CHARS)|code-block|syntax|javascript|1257090|结束输出用法:+DECLARE+@OUT+VARCHAR(40)+EXEC+/*+13，@RANDOM_CHAR+=+@OUT+OUTPUT+SELECT+@OUT+*/|1257091|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

CREATE OR ALTER PROC USP_GENERATE_RANDOM_CHARACTER ( @NO_OF_CHARS INT, @RANDOM_CHAR VARCHAR(40) OUTPUT)
AS
BEGIN
<pre><code>SELECT @RANDOM_CHAR = SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS)
</code></pre>
END
/*
USAGE:
DECLARE @OUT VARCHAR(40)
EXEC USP_GENERATE_RANDOM_CHARACTER 13,@RANDOM_CHAR = @OUT OUTPUT
SELECT @OUT
*/

blocks|key|1471789|text|用于共享的Remus+Rusanu代码-thanks的小修改|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471790|这将生成一个随机字符串，并且可以在不使用种子值的情况下使用|1471791|+++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+%2B+'_@';

set++@seed+=+(rand(@step)*2147483647);


select+@length+=+@minLen+%2B+rand(@seed)+*+(@maxLen-@minLen)
++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);



declare+@dice+int;
select+@dice+=+rand(@seed)+*+len(@first),
++++@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
select+@string+=+substring(@first,+@dice,+1);

while+0+<+@length+
begin
++++select+@dice+=+rand(@seed)+*+100
++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++if+(@dice+<+10)+--+10%25+special+chars
++++begin
++++++++select+@dice+=+rand(@seed)+*+len(@specials)%2B1
++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++++++select+@string+=+@string+%2B+substring(@specials,+@dice,+1);
++++end
++++else+if+(@dice+<+10%2B10)+--+10%25+digits
++++begin
++++++++select+@dice+=+rand(@seed)+*+len(@digit)%2B1
++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);
++++++++select+@string+=+@string+%2B+substring(@digit,+@dice,+1);
++++end
++++else+--+rest+80%25+alpha
++++begin
++++++++declare+@preseed+int+=+@seed;
++++++++select+@dice+=+rand(@seed)+*+len(@alpha)%2B1
++++++++++++,+@seed+=+(rand((@seed%2B@step)%252147483647)*2147483647);

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

++++select+@length+=+@length+-+1

++++end+
++++SELECT+@string+|code-block|syntax|javascript|1471792|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Small modification of Remus Rusanu code -thanks for sharing
This generate a random string and can be used without the seed value
<pre><code> 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 &lt; @length 
begin
 select @dice = rand(@seed) * 100
 , @seed = (rand((@seed+@step)%2147483647)*2147483647);
 if (@dice &lt; 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 &lt; 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 
</code></pre>

blocks|key|1257204|text|另一个具有完整字母表的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1257205|SELECT+LEFT(REPLACE(REPLACE((SELECT+CRYPT_GEN_RANDOM(16)+FOR+XML+PATH(''),+BINARY+BASE64),'%2B',''),'/',''),16);|code-block|syntax|javascript|1257206|用你的长度替换16。|1257207|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Another solution with the complete alphabet:
<pre><code>SELECT LEFT(REPLACE(REPLACE((SELECT CRYPT_GEN_RANDOM(16) FOR XML PATH(''), BINARY BASE64),'+',''),'/',''),16);
</code></pre>
Replace 16 by your length.

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?

Generating random strings with T-SQL

SQL Server

如果你想用T-SQL生成一个伪随机的字母数字字符串，你会怎么做？如何排除像美元符号、破折号和斜杠这样的字符？

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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