Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
A more controllable random string generator function for SQL Server
/* How to generate random strings on SQL Server, While controling their length and the type of chars they can contain. Published on What the # do I know. https://zoharpeled.wordpress.com/2019/12/10/a-more-controllable-random-string-generator-function-for-sql-server/ */ CREATE VIEW dbo.GuidGenerator AS SELECT Newid() As NewGuid; GO CREATE FUNCTION dbo.RandomStringGenerator ( -- the minimum length @MinLength int, -- the maximum length @MaxLength int, -- the maximum number of rows to return. Note: up to 1,000,000 rows @Count int, -- 1, 2 and 4 stands for lower-case, upper-case and digits. -- a bitwise combination of these values can be used to generate all possible combinations: -- 3: lower and upper, 5: lower and digis, 6: upper and digits, 7: lower, upper nad digits @CharType tinyint ) RETURNS TABLE AS RETURN -- An inline tally table with 1,000,000 rows WITH E1(N) AS (SELECT N FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) V(N)), -- 10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E3(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) FROM E3 a, E2 b) --1,000,000 SELECT TOP(@Count) n As Number, ( SELECT TOP (Length) -- choose what char combination to use for the random part CASE @CharType WHEN 1 THEN Lower WHEN 2 THEN Upper WHEN 3 THEN IIF(Rnd % 2 = 0, Lower, Upper) WHEN 4 THEN Digit WHEN 5 THEN IIF(Rnd % 2 = 0, Lower, Digit) WHEN 6 THEN IIF(Rnd % 2 = 0, Upper, Digit) WHEN 7 THEN CASE Rnd % 3 WHEN 0 THEN Lower WHEN 1 THEN Upper ELSE Digit END END FROM Tally As t0 -- create a random number from a guid using the GuidGenerator view CROSS APPLY (SELECT Abs(Checksum(NewGuid)) As Rnd FROM GuidGenerator) As rand CROSS APPLY ( -- generate a random lower-case char, upper-case char and digit SELECT CHAR(97 + Rnd % 26) As Lower, -- Random lower case letter CHAR(65 + Rnd % 26) As Upper,-- Random upper case letter CHAR(48 + Rnd % 10) As Digit -- Random digit ) As Chars WHERE t0.n <> -t1.n -- Needed for the subquery to get re-evaluated for each row FOR XML PATH('') ) As RandomString FROM Tally As t1 CROSS APPLY ( -- Select a random length between @MinLength and @MaxLength (inclusive) SELECT TOP 1 n As Length FROM Tally As t2 CROSS JOIN GuidGenerator WHERE t2.n >= @MinLength AND t2.n <= @MaxLength AND t2.n <> t1.n ORDER BY NewGuid ) As Lengths; GO DECLARE @Count int = 10; SELECT CAST(IntVal.RandomString As Int) As IntValue, UpVal.RandomString as UpperCaseValue, LowVal.RandomString as LowerCaseValue, MixVal.RandomString as MixedValue FROM dbo.RandomStringGenerator(3, 7, @Count, 4) As IntVal JOIN dbo.RandomStringGenerator(10, 10, @Count, 1) As LowVal ON IntVal.Number = LowVal.Number JOIN dbo.RandomStringGenerator(5, 10, @Count, 2) As UpVal ON IntVal.Number = UpVal.Number JOIN dbo.RandomStringGenerator(10, 20, @Count, 7) As MixVal ON IntVal.Number = MixVal.Number
run
|
edit
|
history
|
help
0
SO 51804194 : sql-recursive-cte-replace-statement-too-slow
Demo
SQL_Joins_RankingFunctions
Transitive grouping with recursive sql
MERGE WITH OUTPUT
FOREIGN KEY
tables
Microsoft SQL Server T-SQL in 10 mn ~ Lesson 13 Subqueries samples...
Create MS SQL Server CUSTOMERS Table
Task_7_Final