Generating random strings with T-SQL

For testing scenarios you may need to generate random strings. You can use one of the following ways for this purpose.

declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1);

This script will generate random string of length 5. If you need to increase or decrease number of characters in the generated string, you can add/remove substring statements in the above script.

A more easier approach can be to use NEWID() function, convert the result to varchar and then you can use LEFT(), RIGHT(), SUBSTRING() or any combination of string functions to generate string of desired length.

SELECT CONVERT(varchar(255), NEWID())

This will convert NEWID() output to varchar(255)

SELECT LEFT(CONVERT(varchar(255), NEWID()), 10)

This will display left 10 characters of the NEWID().

SELECT RIGHT(CONVERT(varchar(35), NEWID()), 10)

This will display 10 characters of the NEWID() from right.

SELECT SUBSTRING(CONVERT(varchar(35), NEWID()), 10, 25)

This will display substring of 15 characters starting from index 10 and ends at index 25.