Generating a random alphanumeric value in SQL Server

Alphanumeric value

There can be situations when generating a random string is required (e.g. "LC53WBCA"), especially when it needs to be human-readable. For example, a product code or a ticket identifier. Anything that will be exposed to a non-technical person. In other situations, where uniqueness is primarily the concern of the system, GUIDs or integers are usually more suitable.

The function below was created for the purpose of outputting a (reasonably) unique random value that is also human-eye friendly. To use it, simply call it, passing a random GUID to it, as shown below.

select dbo.GetRandomAlphanumericValue(newid())

The reason why the result of the newid() function must be included as the parameter is because it cannot be used within a user defined function directly. But that's not too much of an inconvenience. The complete code of the UDF for generating random user-friendly strings is below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetRandomAlphanumericValue]
(
	@uniqueIdentifier uniqueidentifier
)
RETURNS varchar(8)
AS
BEGIN

	/*
	------------------------------------------------------------------------
		This function produces a base32 value from the supplied unique identifier.
		The base32 alphabet includes all the upper-case letters and numbers, except for potentially
	ambiguous characters like I, 0, O and 1. L is included because it in upper case form it
	cannot be confused with something else (consider lower case l and I or 1).
	------------------------------------------------------------------------
	*/

	--declare @index int
	declare @rand bigint
	declare @factor bigint 
	declare @byteValue bigint
	declare @randChars bigint

	--set @index = 0
	set @rand = abs(cast( cast (@uniqueIdentifier as binary(8)) as bigint))
	set @factor = 1
	set @randChars = 0


	--Not using 'while' loop here because the check needs to be done at the mid-end of the loop.
	--Checking for the same thing twice is redundant.
	LoopStart:
		set @byteValue = @rand/@factor % 32

		if @bytevalue < 8
		begin
			set @bytevalue = @bytevalue + 50 --Skipping potentially ambigous characters 0 and 1, which can be confused with O and I or l (lower case L)
		end
		else if @bytevalue < 16
		begin
			set @bytevalue = @bytevalue + 57 --Skipping potentially ambigous character I
		end
		else if @bytevalue < 21
		begin
			set @bytevalue = @bytevalue + 58 --Skipping potentially ambigous character O
		end
		else
		begin
			set @bytevalue = @bytevalue + 59
		end
		
		
		set @randChars = @randChars + @bytevalue * @factor
				
		if @factor = 72057594037927936
		begin		
			GoTo LoopEnd
		end
		set @factor = @factor * 256
		GoTo LoopStart
	LoopEnd:

	return cast(cast(@randChars as binary(8)) as varchar(8))
END





Information Error Confirmation required