Getting a Randomly Generated String in SQL

If you want to get a random string in SQL then you could use newid to generate a GUID and take the alphanumeric characters from it, however while these should obviously be unique from each other they don’t contain the full range of characters that may be desired and sequential ones can be very similar to each other if the seed is not reset. GUIDs are also only 32 characters in length (without the hyphens) so may not be as long as required.

A better method is to use the CRYPT_GEN_RANDOM system function to generate binary data of the desired length and to then cast this to Base64. This can’t be used in a function but is allowed in a stored procedure.

create procedure
	sp__get_random_string
	(
	@length int
	) as
declare @randomValue VARBINARY(255)
select @randomValue = CRYPT_GEN_RANDOM(@length)
select cast ('' as xml).value('xs:base64Binary(sql:variable("@randomValue"))', 'varchar(max)')
go

This can then be called like so to generate a string of the desired length.

exec sp__get_random_string 36