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