Converting Hexadecimal String Values to Alpha (ASCII) Strings
-
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
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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?
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Jeff, I was too sleepy to run comparison tests myself last night.
Your input length condition is a nice touch.
But no Jeff, it not a cross-join nor generates 65,536 internal rows. It's a n inner join. You can see it if you check out its execution plan, and it can be replaced with:
----------------------------------------
select @output = @output + char(b.dec)
from dec2hex a inner join dec2hex b
on b.hex = substring(@input,a.dec*2+1,2)
where a.dec*2+1 < LEN(@input)
order by a.dec
----------------------------------------
without affecting its semantics at all.
And dec2hex table is looked-up just LEN(@output) times.(It was 256 times before you add the criteria, though.)
I think it's weird that Richard's function is still much faster. I thought the join routine of the server should be most efficient. Moreover, my function calls substring() half the times of Richard's.
Anyway, it was a very interesting quiz. Thank you all!
-
Heh... and I was too sleepy this morning... I shouldn't post when I'm pooped...
It is still a bit of a half cross join (more specifically, a triangular join and does an index scan of 76 rows) but it certainly isn't 65,536. My mistake. That would also explain why Richard's is still faster...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
I ran mine and Richard's functions on Jeffs test data (pushed up to 1000000 rows) and my function above (at 00:01:25.603) just pipped Richards (at 00:01:57.740).
alter table #HexTest add ASCIIValue varchar (20)
go
if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrial
create table #TimeTrial (Event varchar (20), DateStamp datetime default getdate ())
update #HexTest set ASCIIValue = null -- reset
insert #TimeTrial (Event) values ('RichardStart')
update #HexTest set ASCIIValue = dbo.f_hextostr (HexValue)
insert #TimeTrial (Event) values ('RichardFinish')
select * from #HexTest
update #HexTest set ASCIIValue = null -- reset
insert #TimeTrial (Event) values ('EddieStart')
update #HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue)
insert #TimeTrial (Event) values ('EddieFinish')
select * from #HexTest
select (select DateStamp from #TimeTrial where Event = 'RichardFinish') - (select DateStamp from #TimeTrial where Event = 'RichardStart') Richard
select (select DateStamp from #TimeTrial where Event = 'EddieFinish') - (select DateStamp from #TimeTrial where Event = 'EddieStart') Eddie
Eddie.
-
Hey Eddie!
I like this conversation very much!
Try this with only 100.000 recs!
if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrial
create table #TimeTrial (id integer identity(1,1), Event varchar (20), datestart datetime default getdate (), dateend datetime null)
declare @counter integer
set @counter=1
while @counter<13
begin
insert #TimeTrial (Event) values ('Richard')
update HexTest set ASCIIValue = dbo.f_hextostr (HexValue)
update #TimeTrial set dateend=getdate() where id=@counter
set @counter=@counter+1
insert #TimeTrial (Event) values ('Eddie')
update HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue)
update #TimeTrial set dateend=getdate() where id=@counter
set @counter=@counter+1
end
select *, dateend-datestart as diff from #TimeTrial order by id
The result:
1 Richard 00:00:17.333
2 Eddie 00:00:08.283
3 Richard 00:00:06.610
4 Eddie 00:00:08.010
5 Richard 00:00:06.420
6 Eddie 00:00:08.000
7 Richard 00:00:06.520
8 Eddie 00:01:02.360
9 Richard 00:00:06.460
10 Eddie 00:00:07.780
11 Richard 00:00:06.510
12 Eddie 00:00:07.803
Conclusions: The first runs (1-2) must be eliminated, our data not in cash yet. The runs 7-8 must be eliminated, Your result totally differs from others (
really sometimes I don't know, what the hell is running on background), the rest results are comparable.By the way: our functions are able to run faster, if:
You eliminate the UPPER function (suppose the HEX string is correct);
I eliminate the IF part (suppose the HEX string is correct)
Best regards: Richard
-
Richard yes, your loop does prove your fn to be a bit quicker. Weird how the run time of mine fluctuates so?!
Ho hum.
Good chatting.
Regards, Eddie.
-
I have found using STUFF to create the output to be a little bit faster. I am assuming that it is due to traditional string concantination issues...
ALTER FUNCTION hex2alpha( @input VARCHAR(256) ) RETURNS VARCHAR(256) AS BEGIN DECLARE @output VARCHAR(256); SET @output = SPACE(LEN(@input)/2) SELECT @output = STUFF(@output,a.dec+1,1,CHAR(b.dec)) FROM dec2hex AS a INNER JOIN dec2hex AS b ON b.hex = SUBSTRING(@input,a.dec*2+1,2) WHERE a.dec < LEN(@input)/2 ORDER BY a.dec RETURN @output END -
Firstly my apologies for not responding sooner
Secondly, thank you all for the marvellous responses and ensuing competition. Sadly I must now say my original SQL was a very poor contender over 1,000,000 records, and some of the alternatives, well let's just say WOW!
I have really learned some stuff from you people, so much appreciated.
Stephen
-
Thanks for coming back with that... lots of us end up wondering if we actually helped or confused. Really appreciate the feedback even if it is a bit later than most.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Surely the real lesson from this should be that if something is this hard in SQL you should be doing it in a different language?
I rarely come across an application that is written purely in SQL, so there is usually a presentation layer that is more capable of handling what looks like a purely presentational issue.
Throw away your pocket calculators; visit www.calcResult.com
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply