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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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 32 total)

You must be logged in to reply to this topic. Login to reply