Если вы хотите создать псевдослучайную буквенно-цифровую строку с использованием T-SQL, как бы вы это сделали? Как бы вы исключали из него символы, такие как знаки доллара, тире и косые черты?
Создание случайных строк с помощью T-SQL
Ответ 1
При генерации случайных данных, специально для теста, очень полезно сделать данные случайными, но воспроизводимыми. Секрет состоит в том, чтобы использовать явные семена для случайной функции, так что, когда тест снова запускается с тем же самым семенем, он снова воспроизводит точно такие же строки. Ниже приведен упрощенный пример функции, которая воспроизводит воспроизводимые имена объектов:
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 = '[email protected]# '
select @first = @alpha + '[email protected]';
set @seed = (rand((@[email protected])%2147483647)*2147483647);
select @length = @minLen + rand(@seed) * (@[email protected])
, @seed = (rand((@[email protected])%2147483647)*2147483647);
declare @dice int;
select @dice = rand(@seed) * len(@first),
@seed = (rand((@[email protected])%2147483647)*2147483647);
select @string = substring(@first, @dice, 1);
while 0 < @length
begin
select @dice = rand(@seed) * 100
, @seed = (rand((@[email protected])%2147483647)*2147483647);
if (@dice < 10) -- 10% special chars
begin
select @dice = rand(@seed) * len(@specials)+1
, @seed = (rand((@[email protected])%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((@[email protected])%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((@[email protected])%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: см. комментарии ниже, в исходной процедуре возникла проблема в том, как она продвигала случайное семя. Я обновил код, а также исправил упомянутую проблему отдельно.
Ответ 2
Использование guid
SELECT @randomString = CONVERT(varchar(255), NEWID())
очень короткий...
Ответ 3
Как и в первом примере, но с большей гибкостью:
-- 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.,[email protected]#%^&*'
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
Я забыл упомянуть одну из других функций, которая делает ее более гибкой. Повторяя блоки символов в @CharPool, вы можете увеличить взвешивание определенных символов, чтобы они были более вероятными.
Ответ 4
Используйте следующий код для возврата короткой строки:
SELECT SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)
Ответ 5
Если вы используете 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
Ответ 6
select left(NEWID(),5)
Это вернет 5 левых большинства символов строки guid
Example run
------------
11C89
9DB02
Ответ 7
Вот случайный буквенно-цифровой генератор
print left(replace(newid(),'-',''),@length) //[email protected] is the length of random Num.
Ответ 8
Я не эксперт в T-SQL, но самый простой способ я уже использовал его так:
select char((rand()*25 + 65))+char((rand()*25 + 65))
Это генерирует два char (A-Z, в ascii 65-90).
Ответ 9
Это сработало для меня: мне нужно было создать только три случайных буквенно-цифровых символа для ID, но он мог работать на любой длине до 15 или около того.
declare @DesiredLength as int = 3;
select substring(replace(newID(),'-',''),cast(RAND()*([email protected]) as int),@DesiredLength);
Ответ 10
Я понимаю, что это старый вопрос с множеством прекрасных ответов. Однако, когда я нашел это, я также нашел более новую статью о TechNet Саид Хасани
T-SQL: как создавать случайные пароли
В то время как решение сосредоточено на паролях, оно применяется к общему случаю. Саид работает по различным соображениям, чтобы прийти к решению. Это очень поучительно.
A script, содержащий все кодовые блоки из статьи, доступен отдельно через TechNet Gallery, но я определенно начну с статьи.
Ответ 11
Я использую эту процедуру, которую я разработал, просто ограничивает символы, которые вы хотите отображать во входных переменных, вы также можете определить длину. Надеюсь, что этот формат хорошо, я новичок в переполнении стека.
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)
= '_=+-$£%^&*()"[email protected]~#:',
@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
Ответ 12
Я впервые встретил этот пост в блоге, затем придумал следующую хранимую процедуру для этого, которую я использую в текущем проекте (извините за странное форматирование):
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
Ответ 13
Я сделал это в SQL 2000, создав таблицу, в которой были символы, которые я хотел использовать, создавая представление, которое выбирает символы из этой таблицы, упорядочивая newid(), а затем выбирает верхний символ из этого представления.
CREATE VIEW dbo.vwCodeCharRandom
AS
SELECT TOP 100 PERCENT
CodeChar
FROM dbo.tblCharacter
ORDER BY
NEWID()
...
SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom
Затем вы можете просто вытащить символы из представления и объединить их по мере необходимости.
EDIT: Вдохновленный ответом Стефана...
select top 1 RandomChar from tblRandomCharacters order by newid()
Нет необходимости в просмотре (на самом деле я не уверен, почему я это сделал - код из нескольких лет назад). Вы все равно можете указать символы, которые хотите использовать в таблице.
Ответ 14
Вот что-то основано на New Id.
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)
Ответ 15
Я думал, что поделюсь или вернусь к сообществу... Это ASCII основано, и решение не идеально, но оно работает достаточно хорошо. Наслаждаться, Горан Б.
/*
-- 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))<[email protected]
THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))+1000)+')' --> wrap for decode
WHEN ASCII(SUBSTRING(@inpString,@position,1))>[email protected]
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
Ответ 16
Это использует 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 = '[email protected]#$&'
select @first = @alpha + '[email protected]';
-- Establish our rand seed and store a new seed for next time
set @seed = (rand(@seed)*2147483647);
select @length = @minLen + rand() * (@[email protected]);
--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
Ответ 17
Есть много хороших ответов, но до сих пор ни один из них не позволяет настраиваемый пул символов и работает как значение по умолчанию для столбца. Я хотел бы сделать что-то вроде этого:
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
Ответ 18
Иногда нам нужно много случайных вещей: любовь, доброта, отпуск и т.д. Я собрал несколько случайных генераторов на протяжении многих лет, и это из Pinal Dave и ответ stackoverflow, который я нашел один раз. См. Ниже.
--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
Ответ 19
В SQL Server 2012 + мы могли бы объединить двоичные файлы некоторых (G) UID, а затем сделать base64 на результат.
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)) в подвыборку.
Ответ 20
Поэтому мне понравилось много ответов выше, но я искал нечто более случайное по своей природе. Я также хотел, чтобы явным образом вызывать исключенные символы. Ниже мое решение, используя представление, которое вызывает CRYPT_GEN_RANDOM, чтобы получить криптографическое случайное число. В моем примере я выбрал только случайное число, равное 8 байтам. Обратите внимание, что вы можете увеличить этот размер, а также использовать начальный параметр функции, если хотите. Вот ссылка на документацию: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql
CREATE VIEW [dbo].[VW_CRYPT_GEN_RANDOM_8]
AS
SELECT CRYPT_GEN_RANDOM(8) as [value];
Причина создания представления заключается в том, что 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,'!,@,#,$,%,&,?');
~ Приветствия
Ответ 21
Для одной случайной буквы вы можете использовать:
select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
(abs(checksum(newid())) % 26)+1, 1)
Важное различие между использованием newid() и rand() заключается в том, что если вы возвращаете несколько строк, newid() вычисляется отдельно для каждой строки, а rand() вычисляется один раз для всего запроса.