SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Hexadecimal String Values to Alpha (ASCII) Strings


Converting Hexadecimal String Values to Alpha (ASCII) Strings

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118345 Visits: 41480
That also takes care of the problem when unpredictable lengths occur without a hint of a loop. Nicely done!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jim J
Jim J
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 52
1 (small) loop (but not row-by-row), using temporary lookup table, can handle variable lengths
-------------------------------------------------------------

Create table #HexToAlpha
(
recordid dec(5,0) identity(1,1)
, hexstring varchar(20)
, alphastring varchar(10)
)
Insert into #HexToAlpha values('416E6E61', '')
Insert into #HexToAlpha values('416E6E656C6F75697361', '')
Insert into #HexToAlpha values('416E746F696E65747465', '')
Insert into #HexToAlpha values('4265726E616465747465', ''
Insert into #HexToAlpha values('4265726E617264696E65', ''
) --etc...

create table #NHC (numbr smallint identity(0,1), hex char(2), chr varchar(1))

insert into #NHC (chr) select top 256 null from sysobjects

declare @hexes char(16)
set @hexes = '0123456789ABCDEF'

update #NHC set hex = substring(@hexes,numbr/16+1,1)+ substring(@hexes, numbr- (numbr/16 * 16)+1, 1), chr=char(numbr)

while exists (select * from #hextoalpha where len(hexstring) <> len(alphastring)*2)
update #hextoalpha set alphastring= alphastring +
(select chr from #NHC where substring(hexstring, len(alphastring)*2 +1,2) = hex )
where len(hexstring) <> len(alphastring)*2

drop table #NHC

select * from #hextoalpha
Charles Murugu
Charles Murugu
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 120

why not use Ryan Price's solution?


Gordon Pollokoff
Gordon Pollokoff
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 335

No loops, minimal dynamic sql

Create table #HexToAlpha

(

recordid dec(5,0) identity(1,1)

, hexstring char(20)

, alphastring varchar(20)

)

Insert into #HexToAlpha values('416E6E61737461736961', null)

Insert into #HexToAlpha values('416E6E656C6F75697361', null)

Insert into #HexToAlpha values('416E746F696E65747465', null)

Insert into #HexToAlpha values('4265726E616465747465', null)

Insert into #HexToAlpha values('4265726E617264696E65', null)

Insert into #HexToAlpha values('436872697374656C6C65', null)

Insert into #HexToAlpha values('4368726973746F706572', null)

Insert into #HexToAlpha values('43696E646572656C6C61', null)

Insert into #HexToAlpha values('436C656D656E74696E65', null)

Insert into #HexToAlpha values('4576616E67656C696E65', null)

Insert into #HexToAlpha values('4672616E636973637573', null)

Insert into #HexToAlpha values('467265646572696B7573', null)

Insert into #HexToAlpha values('4777656E646F6C696E65', null)

Insert into #HexToAlpha values('4A61637175656C696E65', null)

Insert into #HexToAlpha values('4B726973746F70686572', null)

Insert into #HexToAlpha values('4D617267756572697461', null)

Insert into #HexToAlpha values('4D617279636C61697265', null)

Insert into #HexToAlpha values('53656261737469616E6F', null)

Insert into #HexToAlpha values('536861756E74656C6C65', null)

Insert into #HexToAlpha values('5768696C68656D696E61', NULL)

DECLARE @SQL NVARCHAR(1000)

--SET @SQL = 'update #HexToAlpha set alphastring = cast(cast((''0x'' + hexstring) as varbinary) as varchar)'

create TABLE #tempsql

(

recordid INT,

sql NVARCHAR(500)

)

SET @SQL = 'insert into #tempsql select recordid,''update #HexToAlpha set alphastring = cast(cast((0x'' + hexstring +'') as varbinary) as varchar)'' from #HexToAlpha'

PRINT @SQL

EXEC sp_executesql @SQL

EXEC sp_execresultset 'select sql + '' where recordid = '' + cast(recordid as nvarchar) from #tempsql'

SELECT * FROM #HexToAlpha

DROP TABLE #HexToAlpha

DROP TABLE #tempsql



Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Steven Hanley
Steven Hanley
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 113

How about:

select convert(varchar,dbo.hexstring_to_binary('0x' + '416E6E61737461736961'))

Here's the function:

ALTER function [dbo].[hexstring_to_binary]
(
@hex_string varchar(max)
)
returns varbinary(max)
as
begin
declare @hex char(2)
declare @position int
declare @count int
declare @binary_value varbinary(max)
set @count = len(@hex_string)
set @binary_value = cast('' as varbinary(1))

if substring(@hex_string, 1, 2) = '0x'
set @position = 3
else
set @position = 1
while (@position <= @count)
begin
set @hex = substring(@hex_string, @position, 2)
set @binary_value = @binary_value +
cast(case when substring(@hex, 1, 1) like '[0-9]'
then cast(substring(@hex, 1, 1) as int)
else cast(ascii(upper(substring(@hex, 1, 1)))-55 as int)
end * 16 +
case when substring(@hex, 2, 1) like '[0-9]'
then cast(substring(@hex, 2, 1) as int)
else cast(ascii(upper(substring(@hex, 2, 1)))-55 as int)
end as binary(1))
set @position = @position + 2
end
return @binary_value
end -- hexstring_to_binary


Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 153

below a function I have been using in a App. Its quite similar to yours. Its fast as it only loops in memory and does not require temp tables. as usual usage examples are in the remarks on the top of the function. The input string size is 4000 characters, therefore it can only return an ascii string of 2000 characters ie 1/2 the size passed in. secondly, if the string passed in is of an odd length, it left pads a 0 before iterating so that it does not crash

create function Hex2String(@strHex as nvarchar(4000))
returns nvarchar(2000)
as
begin
/*
Function : Hex2String
Author : Pratap J Prabhu
Syntax :
> select dbo.Hex2String('6566676869')
> declare @strHexString nvarchar(4000)
declare @strReturnedString nvarchar(4000)
set @strHexString ='65666768'
set @strReturnedString = dbo.Hex2String(@strHexString)
print @strReturnedString
Purpose : passed a hexadecimal encoded string this function returns a Ascii string
Limitations : Max input Hex string size = 4000 characters
Max string size returned = 2000

*/
declare @cChar char(1)
declare @nDeciVal int
declare @cHexBit1 char(1)
declare @cHexBit2 char(1)
declare @strRetVal nvarchar(2000)
declare @iLoop smallint

set @strRetVal=''
set @strHex = ltrim(rtrim(isnull(@strHex,'')))
if @strHex<>''
begin
----- Hex numbers should always have a even length, so lets make it so
if (round(len(@strHex)/2,0)*2)<>len(@strHex)
set @strHex='0'+@strhex

----- Now loop down the length of the HexString handling 2 bits at a time
set @iLoop=1
while @iLoop <len(@strHex)
begin
set @cHexBit1=substring(@strHex, @iLoop , 1) -- The First Bit
set @cHexBit2=substring(@strHex, @iLoop+1, 1) -- the second bit

-- Convert the First Hex Bit value to its equivalent Decimal Value
-- Multiplying by 16 as its in the 10s place
if @cHexBit1>=0 and @cHexBit1<=9
set @nDeciVal=convert(int,@cHexBit1)*16
else
set @nDeciVal=convert(int,ASCII(@cHexBit1)-ASCII('A')+1) * 16

-- Convert the second Hex Bit value to its equivalent Decimal Value
-- do not Multiply by 16 as its in the units place
if @cHexBit2>=0 and @cHexBit2<=9
set @nDeciVal=@nDeciVal+convert(int,@cHexBit2)
else
set @nDeciVal=@nDeciVal+(ASCII(@cHexBit2)-ASCII('A')+1)

-- Store the Ascii Value
set @strRetVal=@strRetVal+ char(@nDeciVal)
set @iLoop=@iLoop+2 -- continue to the next character ie the next 2 Hex Bits
end
end
return (@strRetVal)
end

go



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118345 Visits: 41480

Hey, Richard... just to let you know... I can normally beat most looping functions by using a Tally table... but not this one (least ways, not so far ). Your looping function is about 2 seconds faster that my Tally table solution on a 100,000 row test table. Nice job!

FYI... the direct fixed width solution I posted smokes both the Tally table and the looping inside the function by a full 10 out of 13 seconds.



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JH Park
JH Park
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 12
How about this? No dynamic SQL, no (explicit) loop. Just a permanent lookup table.
====================================================================

declare @dec2hex1 table (
id smallint identity(0,1),
hex1 char(1)
)

insert into @dec2hex1 (hex1)
select top 16 null from sysobjects

update @dec2hex1
set hex1 = substring('0123456789abcdef',id+1,1)

create table dec2hex (
dec smallint identity(0,1),
hex char(2)
)

insert into dec2hex (hex)
select a.hex1+b.hex1
from @dec2hex1 a cross join @dec2hex1 b
order by 1

create unique index ix_dec2hex on dec2hex (hex, dec);


create function hex2alpha (
@input varchar(256)
) returns varchar(256)
begin
declare @output varchar(256)
set @output = ''

select @output = @output + char(b.dec)
from dec2hex a, dec2hex b
where b.hex = substring(@input,a.dec*2+1,2)
order by a.dec

return @output
end


select dbo.hex2alpha('416E6E61737461736961')

============================================
Jeff, can you run a performance test for this?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118345 Visits: 41480

Sure thing, JH... I don't like the looks of that unconstrained cross-join in the function, though... I think it's going to gen 65,536 internal rows every time it executes...

I'll try without and with a limit in the criteria...



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118345 Visits: 41480

Ok... I was right about the cross-join... and it took more than 3 minutes to exec on 100,000 rows.

And when I change that snippet in the function to have a limit (see below), it still takes about 46 seconds (Richard's weigh's in at only 13).

select @output = @output + char(b.dec)
from dec2hex a, dec2hex b
where b.hex = substring(@input,a.dec*2+1,2)
AND a.dec*2+1 < LEN(@input)
order by a.dec

I think you're on the right track using a helper table to do this without a loop or dynamic SQL...

In case anyone else want's to do some testing on the problem, here's the code I used to gen the 100,000 row test table...

--===== Create and populate a 100,000 row test table.
SELECT TOP 100000
RowNum = IDENTITY(INT,1,1),
HexValue = '416E6E61737461736961'
INTO dbo.HexTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2

--===== Add primary key
ALTER TABLE dbo.HexTest
ADD PRIMARY KEY CLUSTERED (RowNum)



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search