Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Converting Hexadecimal String Values to Alpha (ASCII) Strings Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 17, 2007 5:25 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:35 PM Points: 42,070, Visits: 39,452
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #366716
 Posted Thursday, May 17, 2007 8:16 AM
 Forum 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 sysobjectsdeclare @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)*2drop table #NHCselect * from #hextoalpha
Post #366810
 Posted Thursday, May 17, 2007 8:28 AM
 Forum 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 Group: General Forum Members Last Login: Friday, March 25, 2016 6:18 AM Points: 162, 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 @SQLEXEC sp_executesql @SQLEXEC sp_execresultset 'select sql + '' where recordid = '' + cast(recordid as nvarchar) from #tempsql'SELECT * FROM #HexToAlphaDROP TABLE #HexToAlphaDROP 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 Group: General Forum Members Last Login: Tuesday, July 28, 2015 2:26 PM Points: 12, 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)asbegin    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_valueend -- hexstring_to_binary
Post #366951
 Posted Friday, May 18, 2007 3:16 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, May 19, 2016 7:16 PM Points: 139, 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)asbegin /*  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 =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)endgo
Post #367362
 Posted Friday, May 18, 2007 9:20 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:35 PM Points: 42,070, Visits: 39,452
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #367386
 Posted Saturday, May 19, 2007 3:53 AM
 Forum 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 sysobjectsupdate @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 1create 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 @outputendselect dbo.hex2alpha('416E6E61737461736961')============================================Jeff, can you run a performance test for this?
Post #367400
 Posted Saturday, May 19, 2007 10:41 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:35 PM Points: 42,070, Visits: 39,452
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #367420
 Posted Saturday, May 19, 2007 10:54 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:35 PM Points: 42,070, Visits: 39,452
 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 bwhere b.hex = substring(@input,a.dec*2+1,2)AND a.dec*2+1 < LEN(@input)order by a.decI 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." Helpful Links:How to post code problemsHow to post performance problems
Post #367421

 Permissions