Post reply

Converting Hexadecimal String Values to Alpha (ASCII) Strings

  • April 20, 2007 at 10:41 am

    #173729

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/slasham/2969.asp

  • May 16, 2007 at 11:14 pm

    #707008

    did you try

    select

    cast(cast(0x416E6E61737461736961 as varbinary) as varchar)

    ?

     

    /R

  • May 17, 2007 at 12:17 am

    #707013

    He doesn't get it as a hex/binary number... he get's it as a string.

    --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)

  • May 17, 2007 at 12:49 am

    #707017

    Nicely done... If I may suggest, however, a method that avoids both loops and dynamic SQL...

    --===== If the test table exists, drop it

         IF OBJECT_ID('TempDB..#HexToAlpha','U') IS NOT NULL

            DROP TABLE #HexToAlpha

    --===== Supress the auto-display of rowcounts

        SET NOCOUNT ON

    --===== This is your original table creation/population code

    Create table #HexToAlpha

    (

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

     , hexstring char(20)

     , alphastring char(10)

    )

    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)

    --===== Ready to rock... setup a couple of variables

         -- Timer to measure duration

    DECLARE @Start DATETIME

        SET @Start = GETDATE()

         -- The "key" to this demo and the speed of execution

    DECLARE @Numbers VARCHAR(16)

        SET @Numbers = '0123456789ABCDEF'

    --===== Demo the solution for 10 characters

     UPDATE #HexToAlpha

        SET alphastring =

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 1,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString, 2,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 3,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString, 4,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 5,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString, 6,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 7,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString, 8,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 9,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,10,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,11,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,12,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,13,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,14,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,15,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,16,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,17,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,18,1),@Numbers,1) - 1)))

          + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,19,1),@Numbers,1) - 1)

                                + (CHARINDEX(SUBSTRING(HexString,20,1),@Numbers,1) - 1)))

       FROM #HexToAlpha

    --===== Display the duration

      PRINT STR(DATEDIFF(ms,@Start,GETDATE())) + ' Milliseconds duration'

    --===== Display the final contents of the test table

     SELECT * FROM #HexToAlpha

    If the number of characters becomes unpredictable, the use of a Tally table would make the solution almost as easy with only a minor sacrifice in speed.

    --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)

  • May 17, 2007 at 4:26 am

    #707052

    -- Try to use this FUNCTION instead of any temptables, looping, dinamyc SQL and so on.

    if exists (select * from dbo.sysobjects where name = 'f_hextostr' and xtype = 'FN')

    drop function [dbo].[f_hextostr]

    GO

    CREATE FUNCTION [dbo].[f_hextostr] (@hexstring VARCHAR(512))

    RETURNS VARCHAR(256)

    AS

    begin

     declare @char1 char(1), @char2 char(1), @strlen int, @currpos int, @result varchar(256)

     set @strlen=len(@hexstring)

     set @currpos=1

     set @result=''

     while @currpos<@strlen

      begin

       set @char1=substring(@hexstring,@currpos,1)

       set @char2=substring(@hexstring,@currpos+1,1)

       if (@char1 between '0' and '9' or @char1 between 'A' and 'F')

        and (@char2 between '0' and '9' or @char2 between 'A' and 'F')

        set @result=@result+

         char((ascii(@char1)-case when @char1 between '0' and '9' then 48 else 55 end)*16+

         ascii(@char2)-case when @char2 between '0' and '9' then 48 else 55 end)

       set @currpos = @currpos+2

      end

     return @result

    end

    GO

    -- After filling rows of Your example, do simple this:

    update #HexToAlpha set alphastring=dbo.f_hextostr(hexstring)

    Best Regards: Richard

  • May 17, 2007 at 4:56 am

    #707065

    If you use dynamic SQL, you can use something like this:

    DECLARE @String char(20), @stmt nvarchar(500)

    SET @String='416E6E61737461736961'

    IF @String LIKE '%[^0-9A-F]%' RETURN

    SET @stmt='SELECT CONVERT(char(10),0x'+@String+')'

    EXEC (@stmt)

    And if you want to convert the whole table, use something like this:

    Create table #HexToAlpha

    (

    recordid int identity(1,1)

    , hexstring char(20)

    , alphastring char(10)

    )

    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)

    WHILE 1=1 BEGIN

    DECLARE @String char(20), @stmt nvarchar(500), @recordid int

    SET @recordid=NULL

    SELECT TOP 1 @String=hexstring, @recordid=recordid

    FROM #HexToAlpha WHERE alphastring IS NULL

    IF @recordid IS NULL BREAK

    IF @String LIKE '%[^0-9A-F]%' RETURN

    SET @stmt='UPDATE #HexToAlpha SET alphastring=CONVERT(char(10),0x'

    +@String+') WHERE recordid='+CONVERT(varchar(10),@recordid)

    EXEC (@stmt)

    END

    SELECT * FROM #HexToAlpha

    By checking the string against the '%[^0-9A-F]%' pattern, I've tried to avoid any possibility of SQL Injection due to the usage of Dynamic SQL.

    Razvan

  • May 17, 2007 at 5:00 am

    #707067

    or you can build the entire update string at once:

    declare @sql nvarchar(4000)

    set @sql = ''

    select @sql = @sql + ' update #HexToAlpha set alphastring = convert(varchar, 0x' + hexstring + ') where recordid = ' + convert(varchar, recordid)

    from #HexToAlpha

    exec sp_executesql @sql

  • May 17, 2007 at 5:17 am

    #707070

    Heh... I thought you said "without looping".

    --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)

  • May 17, 2007 at 5:21 am

    #707072

    quoteBy checking the string against the '%[^0-9A-F]%' pattern, I've tried to avoid any possibility of SQL Injection due to the usage of Dynamic SQL.

    Nice!

    --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)

  • May 17, 2007 at 5:22 am

    #707073

    Excuse Me!

    Maybe my english is poor (or too short), I'm from Hungary.

    I mean "looping" --> looping on records. The "function" solution loops on simple variables only (in memory), and according to my experiences, its the fastest way.

    Thanks: Richard

  • May 17, 2007 at 5:25 am

    #707074

    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.

    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)

  • May 17, 2007 at 8:16 am

    #707152

    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

  • May 17, 2007 at 8:28 am

    #707158

    why not use Ryan Price's solution?

     

  • May 17, 2007 at 10:43 am

    #707216

    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

  • May 17, 2007 at 2:07 pm

    #707251

    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

     

Viewing 15 posts - 1 through 15 (of 32 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy