Another function for the mix:

if objectproperty (object_id ('dbo.fn_HexToAlpha'), 'istablefunction') = 0 drop function dbo.fn_HexToAlpha

go

create function dbo.fn_HexToAlpha (@HexString char (40))

returns varchar (20) as

begin

declare @Loop tinyint, @AsciiString varchar (20), @Nibble1 char (1), @Nibble2 char (1)

select @AsciiString = '', @Loop = 1

while @Loop < len (@HexString)

 begin

 select @Nibble1 = upper (substring (@HexString, @Loop, 1)), @Nibble2 = upper (substring (@HexString, @Loop + 1, 1))

 set @AsciiString = @AsciiString + char (16 * case @Nibble1 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble1 end + case @Nibble2 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble2 end)

 set @Loop = @Loop + 2

 end

return @AsciiString

end

go

select dbo.fn_HexToAlpha (HexString) from #HexToAlpha

Eddie Lee.