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.