Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Converting Hexadecimal String Values to Alpha (ASCII) Strings Expand / Collapse
Author
Message
Posted Thursday, May 17, 2007 5:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #366716
Posted Thursday, May 17, 2007 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:52 PM
Points: 5, 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
Post #366810
Posted Thursday, May 17, 2007 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2011 3:07 PM
Points: 3, Visits: 120

why not use Ryan Price's solution?

 

Post #366817
Posted Thursday, May 17, 2007 10:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 18, 2012 8:55 AM
Points: 162, Visits: 334

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
Post #366896
Posted Thursday, May 17, 2007 2:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 5:31 PM
Points: 12, Visits: 108

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

 

Post #366951
Posted Friday, May 18, 2007 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148

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


 

 

Post #367362
Posted Friday, May 18, 2007 9:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #367386
Posted Saturday, May 19, 2007 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 14, 2010 10:55 PM
Points: 4, 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?
Post #367400
Posted Saturday, May 19, 2007 10:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #367420
Posted Saturday, May 19, 2007 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #367421
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse