Gdybyś chciał wygenerować pseudolosowy ciąg alfanumeryczny za pomocą T-SQL, jak byś to zrobił? Jak wykluczyłbyś z niego znaki takie jak znaki dolara, myślniki i ukośniki?
Gdybyś chciał wygenerować pseudolosowy ciąg alfanumeryczny za pomocą T-SQL, jak byś to zrobił? Jak wykluczyłbyś z niego znaki takie jak znaki dolara, myślniki i ukośniki?
Odpowiedzi:
Podczas generowania losowych danych, szczególnie do testów, bardzo przydatne jest, aby dane były losowe, ale odtwarzalne. Sekret polega na użyciu jawnych nasion dla funkcji losowej, tak aby po ponownym uruchomieniu testu z tym samym ziarnem wytwarzał ponownie dokładnie te same ciągi. Oto uproszczony przykład funkcji, która generuje nazwy obiektów w odtwarzalny sposób:
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
Podczas uruchamiania testów obiekt wywołujący generuje losowe ziarno, które kojarzy z przebiegiem testowym (zapisuje je w tabeli wyników), a następnie przekazuje je razem z nasionem, podobnie do tego:
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;
Aktualizacja 17.02.2016: Zobacz komentarze poniżej, pierwotna procedura miała problem ze sposobem, w jaki przesuwała losowe ziarno. Zaktualizowałem kod, a także naprawiłem wspomniany problem oddzielania po jednym.
@seed = rand(@seed+1)*2147483647. Dla wartości 529126 następną wartością jest 1230039262, a następną wartością jest 192804. Po tym sekwencja jest kontynuowana identycznie. Dane wyjściowe powinny różnić się pierwszym znakiem, ale nie z powodu błędu off-by-one: SUBSTRING(..., 0, ...)zwraca pusty ciąg dla indeksu 0, a dla 529126 ten „ukrywa” pierwszy wygenerowany znak. Poprawka polega na obliczeniu, @dice = rand(@seed) * len(@specials)+1aby indeksy były oparte na 1.
+1ustawiając rand(@seed+1)sam be w samym sobie jako losowy i określany tylko na podstawie początkowego ziarna. W ten sposób, nawet jeśli seria osiągnie tę samą wartość, natychmiast się rozchodzą.
Korzystanie z guid
SELECT @randomString = CONVERT(varchar(255), NEWID())
bardzo krótki ...
Podobnie jak w pierwszym przykładzie, ale z większą elastycznością:
-- min_length = 8, max_length = 12
SET @Length = RAND() * 5 + 8
-- SET @Length = RAND() * (max_length - min_length + 1) + min_length
-- define allowable character explicitly - easy to read this way an easy to
-- omit easily confused chars like l (ell) and 1 (one) or 0 (zero) and O (oh)
SET @CharPool =
'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
SET @PoolLength = Len(@CharPool)
SET @LoopCount = 0
SET @RandomString = ''
WHILE (@LoopCount < @Length) BEGIN
SELECT @RandomString = @RandomString +
SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength), 1)
SELECT @LoopCount = @LoopCount + 1
END
Zapomniałem wspomnieć o jednej z innych funkcji, które sprawiają, że jest to bardziej elastyczne. Powtarzając bloki znaków w @CharPool, możesz zwiększyć wagę niektórych znaków, aby były bardziej prawdopodobne, że zostaną wybrane.
CONVERT(int, RAND() * @PoolLength) + 1(zwróć uwagę na dodane +1). SUBSTRING w T-SQL zaczyna się od indeksu 1, więc w obecnym kształcie ta funkcja czasami dodaje pusty ciąg (gdy indeks wynosi 0) i nigdy nie dodaje ostatniego znaku z @CharPool.
Jeśli używasz SQL Server 2008 lub nowszego, możesz użyć nowej funkcji kryptograficznej crypt_gen_random (), a następnie użyć kodowania base64, aby utworzyć z niej ciąg. Będzie to działać dla maksymalnie 8000 znaków.
declare @BinaryData varbinary(max)
, @CharacterData varchar(max)
, @Length int = 2048
set @BinaryData=crypt_gen_random (@Length)
set @CharacterData=cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryData"))', 'varchar(max)')
print @CharacterData
select left(NEWID(),5)
To zwróci 5 najbardziej wysuniętych na lewo znaków ciągu guid
Example run
------------
11C89
9DB02
Oto losowy generator alfanumeryczny
print left(replace(newid(),'-',''),@length) //--@length is the length of random Num.
Dla jednej losowej litery możesz użyć:
select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
(abs(checksum(newid())) % 26)+1, 1)
Istotna różnica między użyciem newid()a rand()tym, że zwracasz wiele wierszy, newid()jest obliczana osobno dla każdego wiersza, podczas gdy rand()jest obliczana raz dla całego zapytania.
To zadziałało dla mnie: potrzebowałem wygenerować tylko trzy losowe znaki alfanumeryczne dla identyfikatora, ale może działać dla dowolnej długości do 15 lub więcej.
declare @DesiredLength as int = 3;
select substring(replace(newID(),'-',''),cast(RAND()*(31-@DesiredLength) as int),@DesiredLength);
Istnieje wiele dobrych odpowiedzi, ale jak dotąd żadna z nich nie zezwala na konfigurowalną pulę znaków i nie działa jako wartość domyślna dla kolumny. Chciałem móc zrobić coś takiego:
alter table MY_TABLE add MY_COLUMN char(20) not null
default dbo.GenerateToken(crypt_gen_random(20))
Więc wymyśliłem to. Uważaj na zakodowaną liczbę 32, jeśli ją modyfikujesz.
-- 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
Zdaję sobie sprawę, że to stare pytanie z wieloma dobrymi odpowiedziami. Jednak kiedy to znalazłem, znalazłem również nowszy artykuł w TechNet autorstwa Saeid Hasani
T-SQL: jak generować losowe hasła
Chociaż rozwiązanie koncentruje się na hasłach, ma zastosowanie do ogólnego przypadku. Saeid analizuje różne kwestie, aby znaleźć rozwiązanie. To jest bardzo pouczające.
Skrypt zawierający wszystkie bloki kodu z artykułu jest dostępny osobno w Galerii TechNet , ale zdecydowanie zacznę od tego artykułu.
Używam tej procedury, którą opracowałem, po prostu wzmacniając znaki, które chcesz wyświetlać w zmiennych wejściowych, możesz też zdefiniować długość. Mam nadzieję, że ten format dobrze się formatuje, jestem nowy w przepełnieniu stosu.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'GenerateARandomString'))
DROP PROCEDURE GenerateARandomString
GO
CREATE PROCEDURE GenerateARandomString
(
@DESIREDLENGTH INTEGER = 100,
@NUMBERS VARCHAR(50)
= '0123456789',
@ALPHABET VARCHAR(100)
='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
@SPECIALS VARCHAR(50)
= '_=+-$£%^&*()"!@~#:',
@RANDOMSTRING VARCHAR(8000) OUT
)
AS
BEGIN
-- Author David Riley
-- Version 1.0
-- You could alter to one big string .e.e numebrs , alpha special etc
-- added for more felxibility in case I want to extend i.e put logic in for 3 numbers, 2 pecials 3 numbers etc
-- for now just randomly pick one of them
DECLARE @SWAP VARCHAR(8000); -- Will be used as a tempoary buffer
DECLARE @SELECTOR INTEGER = 0;
DECLARE @CURRENTLENGHT INTEGER = 0;
WHILE @CURRENTLENGHT < @DESIREDLENGTH
BEGIN
-- Do we want a number, special character or Alphabet Randonly decide?
SET @SELECTOR = CAST(ABS(CHECKSUM(NEWID())) % 3 AS INTEGER); -- Always three 1 number , 2 alphaBET , 3 special;
IF @SELECTOR = 0
BEGIN
SET @SELECTOR = 3
END;
-- SET SWAP VARIABLE AS DESIRED
SELECT @SWAP = CASE WHEN @SELECTOR = 1 THEN @NUMBERS WHEN @SELECTOR = 2 THEN @ALPHABET ELSE @SPECIALS END;
-- MAKE THE SELECTION
SET @SELECTOR = CAST(ABS(CHECKSUM(NEWID())) % LEN(@SWAP) AS INTEGER);
IF @SELECTOR = 0
BEGIN
SET @SELECTOR = LEN(@SWAP)
END;
SET @RANDOMSTRING = ISNULL(@RANDOMSTRING,'') + SUBSTRING(@SWAP,@SELECTOR,1);
SET @CURRENTLENGHT = LEN(@RANDOMSTRING);
END;
END;
GO
DECLARE @RANDOMSTRING VARCHAR(8000)
EXEC GenerateARandomString @RANDOMSTRING = @RANDOMSTRING OUT
SELECT @RANDOMSTRING
Czasami potrzebujemy wielu przypadkowych rzeczy: miłości, uprzejmości, wakacji itp. Przez lata zebrałem kilka losowych generatorów, a są to Pinal Dave i odpowiedź, którą znalazłem raz. Odniesienia poniżej.
--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
Oto jeden, który wymyśliłem dzisiaj (ponieważ nie podobała mi się żadna z istniejących odpowiedzi).
Ten generuje tymczasową tabelę losowych ciągów na podstawie newid() , ale obsługuje również niestandardowy zestaw znaków (więc więcej niż tylko 0-9 i AF), niestandardową długość (do 255, limit jest zakodowany na stałe, ale może być zmieniony) i niestandardową liczbę losowych rekordów.
Oto kod źródłowy (miejmy nadzieję, że komentarze pomogą):
/**
* First, we're going to define the random parameters for this
* snippet. Changing these variables will alter the entire
* outcome of this script. Try not to break everything.
*
* @var {int} count The number of random values to generate.
* @var {int} length The length of each random value.
* @var {char(62)} charset The characters that may appear within a random value.
*/
-- Define the parameters
declare @count int = 10
declare @length int = 60
declare @charset char(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
/**
* We're going to define our random table to be twice the maximum
* length (255 * 2 = 510). It's twice because we will be using
* the newid() method, which produces hex guids. More later.
*/
-- Create the random table
declare @random table (
value nvarchar(510)
)
/**
* We'll use two characters from newid() to make one character in
* the random value. Each newid() provides us 32 hex characters,
* so we'll have to make multiple calls depending on length.
*/
-- Determine how many "newid()" calls we'll need per random value
declare @iterations int = ceiling(@length * 2 / 32.0)
/**
* Before we start making multiple calls to "newid", we need to
* start with an initial value. Since we know that we need at
* least one call, we will go ahead and satisfy the count.
*/
-- Iterate up to the count
declare @i int = 0 while @i < @count begin set @i = @i + 1
-- Insert a new set of 32 hex characters for each record, limiting to @length * 2
insert into @random
select substring(replace(newid(), '-', ''), 1, @length * 2)
end
-- Now fill the remaining the remaining length using a series of update clauses
set @i = 0 while @i < @iterations begin set @i = @i + 1
-- Append to the original value, limit @length * 2
update @random
set value = substring(value + replace(newid(), '-', ''), 1, @length * 2)
end
/**
* Now that we have our base random values, we can convert them
* into the final random values. We'll do this by taking two
* hex characters, and mapping then to one charset value.
*/
-- Convert the base random values to charset random values
set @i = 0 while @i < @length begin set @i = @i + 1
/**
* Explaining what's actually going on here is a bit complex. I'll
* do my best to break it down step by step. Hopefully you'll be
* able to follow along. If not, then wise up and come back.
*/
-- Perform the update
update @random
set value =
/**
* Everything we're doing here is in a loop. The @i variable marks
* what character of the final result we're assigning. We will
* start off by taking everything we've already done first.
*/
-- Take the part of the string up to the current index
substring(value, 1, @i - 1) +
/**
* Now we're going to convert the two hex values after the index,
* and convert them to a single charset value. We can do this
* with a bit of math and conversions, so function away!
*/
-- Replace the current two hex values with one charset value
substring(@charset, convert(int, convert(varbinary(1), substring(value, @i, 2), 2)) * (len(@charset) - 1) / 255 + 1, 1) +
-- (1) -------------------------------------------------------^^^^^^^^^^^^^^^^^^^^^^^-----------------------------------------
-- (2) ---------------------------------^^^^^^^^^^^^^^^^^^^^^^11111111111111111111111^^^^-------------------------------------
-- (3) --------------------^^^^^^^^^^^^^2222222222222222222222222222222222222222222222222^------------------------------------
-- (4) --------------------333333333333333333333333333333333333333333333333333333333333333---^^^^^^^^^^^^^^^^^^^^^^^^^--------
-- (5) --------------------333333333333333333333333333333333333333333333333333333333333333^^^4444444444444444444444444--------
-- (6) --------------------5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555^^^^----
-- (7) ^^^^^^^^^^^^^^^^^^^^66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666^^^^
/**
* (1) - Determine the two hex characters that we'll be converting (ex: 0F, AB, 3C, etc.)
* (2) - Convert those two hex characters to a a proper hexadecimal (ex: 0x0F, 0xAB, 0x3C, etc.)
* (3) - Convert the hexadecimals to integers (ex: 15, 171, 60)
* (4) - Determine the conversion ratio between the length of @charset and the range of hexadecimals (255)
* (5) - Multiply the integer from (3) with the conversion ratio from (4) to get a value between 0 and (len(@charset) - 1)
* (6) - Add 1 to the offset from (5) to get a value between 1 and len(@charset), since strings start at 1 in SQL
* (7) - Use the offset from (6) and grab a single character from @subset
*/
/**
* All that is left is to add in everything we have left to do.
* We will eventually process the entire string, but we will
* take things one step at a time. Round and round we go!
*/
-- Append everything we have left to do
substring(value, 2 + @i, len(value))
end
-- Select the results
select value
from @random
To nie jest procedura składowana, ale przekształcenie jej w jedną nie byłoby trudne. Nie jest też horrendalnie powolny (wygenerowanie 1000 wyników o długości 60 zajęło mi ~ 0,3 sekundy, czyli więcej niż kiedykolwiek będę potrzebował osobiście), co było jednym z moich początkowych zmartwień z powodu wszystkich mutacji struny, które robię.
Głównym wnioskiem jest to, że nie próbuję tworzyć własnego generatora liczb losowych, a mój zestaw znaków nie jest ograniczony. Po prostu używam generatora losowego, który ma SQL (wiem, że istnieje rand(), ale to nie jest dobre dla wyników tabeli). Miejmy nadzieję, że to podejście łączy tutaj dwa rodzaje odpowiedzi, od zbyt prostych (tj. Po prostu newid()) i zbyt złożonych (tj. Niestandardowy algorytm liczb losowych).
Jest też krótki (bez komentarzy) i łatwy do zrozumienia (przynajmniej dla mnie), co jest zawsze plusem w mojej książce.
Jednak ta metoda nie może być zapoczątkowana, więc za każdym razem będzie naprawdę losowa i nie będzie można replikować tego samego zestawu danych z żadną niezawodnością. OP nie wymienia tego jako wymogu, ale wiem, że niektórzy ludzie szukają tego typu rzeczy.
Wiem, że spóźniłem się na przyjęcie tutaj, ale mam nadzieję, że komuś to się przyda.
W przypadku programu SQL Server 2016 i nowszych wersji jest to naprawdę proste i stosunkowo wydajne wyrażenie do generowania kryptograficznie losowych ciągów znaków o danej długości bajtów:
--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))
Zwróć uwagę, że długość bajtu nie jest taka sama, jak zakodowany rozmiar; użyj następujących z tego artykułu do konwersji:
Bytes = 3 * (LengthInCharacters / 4) - Padding
Najpierw natknąłem się na ten post na blogu , a następnie wymyśliłem następującą procedurę składowaną, której używam w bieżącym projekcie (przepraszam za dziwne formatowanie):
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
Zrobiłem to w SQL 2000, tworząc tabelę zawierającą znaki, których chciałem użyć, tworząc widok, który wybiera znaki z tej tabeli w kolejności przez newid (), a następnie wybierając pierwszy pierwszy znak z tego widoku.
CREATE VIEW dbo.vwCodeCharRandom
AS
SELECT TOP 100 PERCENT
CodeChar
FROM dbo.tblCharacter
ORDER BY
NEWID()
...
SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom
Następnie możesz po prostu wyciągać znaki z widoku i łączyć je w razie potrzeby.
EDYCJA: Zainspirowana odpowiedzią Stephana ...
select top 1 RandomChar from tblRandomCharacters order by newid()
Nie ma potrzeby przeglądania (właściwie nie jestem pewien, dlaczego to zrobiłem - kod pochodzi sprzed kilku lat). Nadal możesz określić znaki, których chcesz używać w tabeli.
Oto coś opartego na Nowym 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)
Pomyślałem, że podzielę się lub oddam społeczności ... Jest oparty na ASCII, a rozwiązanie nie jest idealne, ale działa całkiem dobrze. Ciesz się, Goran B.
/*
-- predictable masking of ascii chars within a given decimal range
-- purpose:
-- i needed an alternative to hashing alg. or uniqueidentifier functions
-- because i wanted to be able to revert to original char set if possible ("if", the operative word)
-- notes: wrap below in a scalar function if desired (i.e. recommended)
-- by goran biljetina (2014-02-25)
*/
declare
@length int
,@position int
,@maskedString varchar(500)
,@inpString varchar(500)
,@offsetAsciiUp1 smallint
,@offsetAsciiDown1 smallint
,@ipOffset smallint
,@asciiHiBound smallint
,@asciiLoBound smallint
set @ipOffset=null
set @offsetAsciiUp1=1
set @offsetAsciiDown1=-1
set @asciiHiBound=126 --> up to and NOT including
set @asciiLoBound=31 --> up from and NOT including
SET @inpString = '{"config":"some string value", "boolAttr": true}'
SET @length = LEN(@inpString)
SET @position = 1
SET @maskedString = ''
--> MASK:
---------
WHILE (@position < @length+1) BEGIN
SELECT @maskedString = @maskedString +
ISNULL(
CASE
WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
THEN
CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
(case when @ipOffset is null then
case when ASCII(SUBSTRING(@inpString,@position,1))%2=0 then @offsetAsciiUp1 else @offsetAsciiDown1 end
else @ipOffset end))
WHEN ASCII(SUBSTRING(@inpString,@position,1))<=@asciiLoBound
THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))+1000)+')' --> wrap for decode
WHEN ASCII(SUBSTRING(@inpString,@position,1))>=@asciiHiBound
THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@inpString,@position,1))+1000)+')' --> wrap for decode
END
,'')
SELECT @position = @position + 1
END
select @MaskedString
SET @inpString = @maskedString
SET @length = LEN(@inpString)
SET @position = 1
SET @maskedString = ''
--> UNMASK (Limited to within ascii lo-hi bound):
-------------------------------------------------
WHILE (@position < @length+1) BEGIN
SELECT @maskedString = @maskedString +
ISNULL(
CASE
WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
THEN
CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
(case when @ipOffset is null then
case when ASCII(SUBSTRING(@inpString,@position,1))%2=1 then @offsetAsciiDown1 else @offsetAsciiUp1 end
else @ipOffset*(-1) end))
ELSE ''
END
,'')
SELECT @position = @position + 1
END
select @maskedString
Używa rand z ziarnem, takim jak jedna z innych odpowiedzi, ale nie jest konieczne dostarczanie ziarna przy każdym wywołaniu. Podanie go przy pierwszym połączeniu jest wystarczające.
To jest mój zmodyfikowany kod.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'usp_generateIdentifier'))
DROP PROCEDURE usp_generateIdentifier
GO
create procedure usp_generateIdentifier
@minLen int = 1
, @maxLen int = 256
, @seed int output
, @string varchar(8000) output
as
begin
set nocount on;
declare @length int;
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)
select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
, @digit = '1234567890'
, @specials = '_@#$&'
select @first = @alpha + '_@';
-- Establish our rand seed and store a new seed for next time
set @seed = (rand(@seed)*2147483647);
select @length = @minLen + rand() * (@maxLen-@minLen);
--print @length
declare @dice int;
select @dice = rand() * len(@first);
select @string = substring(@first, @dice, 1);
while 0 < @length
begin
select @dice = rand() * 100;
if (@dice < 10) -- 10% special chars
begin
select @dice = rand() * len(@specials)+1;
select @string = @string + substring(@specials, @dice, 1);
end
else if (@dice < 10+10) -- 10% digits
begin
select @dice = rand() * len(@digit)+1;
select @string = @string + substring(@digit, @dice, 1);
end
else -- rest 80% alpha
begin
select @dice = rand() * len(@alpha)+1;
select @string = @string + substring(@alpha, @dice, 1);
end
select @length = @length - 1;
end
end
go
W SQL Server 2012+ mogliśmy połączyć pliki binarne niektórych (G) UID, a następnie wykonać konwersję wyniku na 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
;
Jeśli potrzebujesz dłuższych ciągów (lub widzisz =w wyniku znaki), musisz dodać więcej + CAST(newid() as varbinary(max))w subselekcji.
Więc podobało mi się wiele powyższych odpowiedzi, ale szukałem czegoś, co ma charakter bardziej przypadkowy. Chciałem też sposób na wyraźne wywołanie wykluczonych postaci. Poniżej znajduje się moje rozwiązanie wykorzystujące widok, który wywołuje kod w CRYPT_GEN_RANDOMcelu uzyskania losowej liczby kryptograficznej. W moim przykładzie wybrałem tylko liczbę losową o wielkości 8 bajtów. Pamiętaj, że jeśli chcesz, możesz zwiększyć ten rozmiar, a także wykorzystać parametr seed funkcji. Oto link do dokumentacji: 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];
Powodem tworzenia widoku jest to, że CRYPT_GEN_RANDOMnie można go wywołać bezpośrednio z funkcji.
Stamtąd utworzyłem funkcję skalarną, która akceptuje długość i parametr ciągu, który może zawierać rozdzielany przecinkami ciąg wykluczonych znaków.
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
Poniżej znajduje się przykład wywołania funkcji.
SELECT [dbo].[fn_GenerateRandomString](8,'!,@,#,$,%,&,?');
~ Pozdrawiam
to bardzo proste. użyj go i ciesz się.
CREATE VIEW [dbo].[vwGetNewId]
AS
SELECT NEWID() AS Id
Creat FUNCTION [dbo].[fnGenerateRandomString](@length INT = 8)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @result CHAR(2000);
DECLARE @String VARCHAR(2000);
SET @String = 'abcdefghijklmnopqrstuvwxyz' + --lower letters
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' + --upper letters
'1234567890'; --number characters
SELECT @result =
(
SELECT TOP (@length)
SUBSTRING(@String, 1 + number, 1) AS [text()]
FROM master..spt_values
WHERE number < DATALENGTH(@String)
AND type = 'P'
ORDER BY
(
SELECT TOP 1 Id FROM dbo.vwGetNewId
) --instead of using newid()
FOR XML PATH('')
);
RETURN @result;
END;
Spowoduje to utworzenie ciągu o długości 96 znaków z zakresu Base64 (górne, dolne, liczby, + i /). Dodanie 3 „NEWID ()” zwiększy długość o 32, bez dopełnienia Base64 (=).
SELECT
CAST(
CONVERT(NVARCHAR(MAX),
CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
+CONVERT(VARBINARY(8), NEWID())
,2)
AS XML).value('xs:base64Binary(xs:hexBinary(.))', 'VARCHAR(MAX)') AS StringValue
Jeśli stosujesz to do zestawu, upewnij się, że wprowadziłeś coś z tego zestawu, aby NEWID () zostało ponownie obliczone, w przeciwnym razie otrzymasz tę samą wartość za każdym razem:
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