Generating random strings with T-SQL
When generating random data, specially for test, it is very useful to make the data random, but reproducible. The secret is to use explicit seeds for the random function, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a function that generates object names in a reproducible manner:
alter procedure usp_generateIdentifier
@minLen int = 1
, @maxLen int = 256
, @seed int output
, @string varchar(8000) output
as
begin
set nocount on;
declare @length int;
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)
declare @step bigint = rand(@seed) * 2147483647;
select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
, @digit = '1234567890'
, @specials = '_@# '
select @first = @alpha + '_@';
set @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
declare @dice int;
select @dice = rand(@seed) * len(@first),
@seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = substring(@first, @dice, 1);
while 0 < @length
begin
select @dice = rand(@seed) * 100
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
if (@dice < 10) -- 10% special chars
begin
select @dice = rand(@seed) * len(@specials)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@specials, @dice, 1);
end
else if (@dice < 10+10) -- 10% digits
begin
select @dice = rand(@seed) * len(@digit)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@digit, @dice, 1);
end
else -- rest 80% alpha
begin
declare @preseed int = @seed;
select @dice = rand(@seed) * len(@alpha)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@alpha, @dice, 1);
end
select @length = @length - 1;
end
end
go
When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:
declare @seed int;
declare @string varchar(256);
select @seed = 1234; -- saved start seed
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
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.
Generating random string in T-SQL
This is far from an optimal solution, but it will work as specified:
select char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
Example output:
WCZOVRCIRELAJUT
Default random 10 character string value for SQL Server column
Expanding on what Deigo Garbar has already suggested.
If you want to use the expression as the default value in your table you would need to put this expression in a function.
But the problem is a UDF function will not allow you to use NEWID() function in it, it will complain about cannot use non-deterministic function in UDF bla bla....
A way around will be to create a view first which simply calls this expression then use that view inside your function and then use that function as Default value for your table.
View
CREATE VIEW dbo.vw_Function_Base
AS
SELECT substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 10) AS Rand_Value
Function
CREATE FUNCTION dbo.fn_getRandom_Value()
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @Rand_Value VARCHAR(10);
SELECT @Rand_Value = Rand_Value
FROM vw_Function_Base
RETURN @Rand_Value;
END
Now you can use this function as default value in your table.
Test
CREATE TABLE SomeTest_Table
( ID INT
, RandValue VARCHAR(10) DEFAULT dbo.fn_getRandom_Value()
)
GO
INSERT INTO SomeTest_Table(ID)
VALUES (1)
GO
SELECT * FROM SomeTest_Table
Important Note
I am not convinced this Part of GUID value will always be unique, so be careful.
How can I generate random strings in TSQL
This will give you 10,000 rows in the specified format.
DECLARE @Numbers TABLE
(
n INT PRIMARY KEY
);
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), --2
E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --256
E16(N) AS (SELECT 1 FROM E08 a, E08 b) --65,536
INSERT INTO @Numbers
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM E16
SELECT CAST((SELECT TOP 20 CHAR(CASE
WHEN Abs(Checksum(Newid()))%2 = 0 THEN 65
ELSE 97
END + Abs(Checksum(Newid()))%26)
FROM @Numbers n1
WHERE n1.n >= -n2.n /*So it gets re-evaluated for each row!*/
FOR XML PATH('')) AS CHAR(20))
FROM @Numbers n2
What is the most efficient way to generate 8 character random alphanumeric string in TSQL?
Here are 2 ways of solving it
Method 1: This will generate 8 random characters and store them in varchar @r, in order to prevent all the individual characters from being identical, I added a seed to RAND().
DECLARE @r varchar(8)
SELECT @r = coalesce(@r, '') +CHAR(
CASE WHEN r between 0 and 9 THEN 48
WHEN r between 10 and 35 THEN 55
ELSE 61 END + r)
FROM
master..spt_values
CROSS JOIN
(SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
WHERE type = 'P' AND number < 8
Method 2: This method is using a tally table, each character will never occur more than once. The rows used to generate these characters are randomized by ordering on newid()
DECLARE @r varchar(8)
SELECT @r = coalesce(@r, '') + n
FROM (SELECT top 8
CHAR(number) n FROM
master..spt_values
WHERE type = 'P' AND
(number between ascii(0) and ascii(9)
or number between ascii('A') and ascii('Z')
or number between ascii('a') and ascii('z'))
ORDER BY newid()) a
In order to ensure uniqueness for each varchar(8) you can store the results in a table and compare with result in that table. You can also make the varchar longer and just hope for the best
Generate random numbers, letters or characters within a range
Some work required for a complete solution but here's the workings of an idea you might want to experiment with further, if you still need it:
declare @type varchar(10)='letters', @length tinyint=5;
with chars as (
select top(59) 31 + Row_Number() over (order by (select 1)) n from master.dbo.spt_values
), s as (
select top (@length) Char(n.n) c
from chars n
where @type='all'
or (@type='symbols' and n between 33 and 47)
or (@type='numbers' and n between 48 and 57)
or (@type='letters' and n between 65 and 90)
order by newid()
)
select String_Agg(s.c,'')
from s
Auto string generator
You can use something like on the below lines.
DECLARE @AlLChars varchar(100) = 'ABCDEFGHIJKL'
DECLARE @Numerics varchar(100) = '12345678910'
DECLARE @SpecialChars varchar(10) = '*+=#/%&!?'
DECLARE @I INT = 1
WHILE @I <= 400
BEGIN
INSERT INTO tblStrings
SELECT
RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%26) + 1 ),1)
+RIGHT( LEFT(@Numeric,ABS(BINARY_CHECKSUM(NEWID())%10) + 1 ),1)
+RIGHT( LEFT(@SpecialChars,ABS(BINARY_CHECKSUM(NEWID())%9) + 1 ),1)
+RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%26) + 1 ),1)
+RIGHT( LEFT(@SpecialChars,ABS(BINARY_CHECKSUM(NEWID())%9) + 1 ),1)
SET @I = @I + 1
END;
Related Topics
What Is Causing Foreign Key Mismatch Error
Store Multiple Bit Values in a Single Table Column
Select Corresponding to Row from the Same Table SQL Server
Bulk Insert Using Stored Procedure
Hive Query Performance for High Cardinality Field
Best Way to Do Nested Case Statement Logic in SQL Server
How to Group by Month from Date Field Using SQL
What Are the Principles Behind, and Benefits Of, the "Party Model"
Search for "Whole Word Match" with SQL Server Like Pattern
Use Select Inside an Update Query
How to Alter This Computed Column in SQL Server 2008
Parameterise Table Name in .Net/Sql
Postgresql: Insert into ... (Select * ...)
SQL Server Select Where Any Column Contains 'X'
Get Most Common Value for Each Value of Another Column in SQL