Generating random hex strings using MS SQL Server

In the post Generating random numbers in a specific range using MS SQL Server we saw how it is possible to generate random integer number for a given range. We will use the function dbo.random_range from this post in order to create a function that generates a random string of hex numbers of any length.
The idea is very simple. Suppose we would like to generate a string of length N, then all we have to do is loop N times and generate a random number POSITION in the range of 1 to 16. For each of the random values we take a substring of one character in the generated POSITION from a predefined string containing all Hex digits (“0” to “9” and “a” to “f”). After we done we will have a string containing N Hex digits.
Let’s have a look at the SQL SERVER code:

CREATE FUNCTION random_hex(@length int)
RETURNS varchar(2000)
AS
BEGIN
  DECLARE @index int
  DECLARE @seq varchar(16) 
  DECLARE @result varchar(2000)
  DECLARE @digit char(1)
  SET @seq = '0123456789abcdef'
  SET @index = 0
  SET @result = ''
  WHILE @index < @length
  BEGIN
    SET @digit = SUBSTRING(@seq, dbo.random_range(1, 16), 1)
    SET @result = @result + @digit
    SET @index = @index + 1
  END 
  RETURN @result
END
GO