Converting Hexadecimal String Values to Alpha (ASCII) Strings

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

  • did you try

    select

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

    ?

     

    /R

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

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

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

  • 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

  • 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

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

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

  • 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

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

  • 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

  • why not use Ryan Price's solution?

     

  • 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

  • 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