Converting Hex String to Int

  • When converting a constant hex string '09FF000' to INT. It is simple as below

    Select Convert(Int, 0x09FF000) --just added 0x

    However when hex string is in a table and stored as varchar. So I tried following

    Declare @hex varchar(50)

    Select @hex = '0x' + '09FF000' --Retrieve hex string from a table

    Select Convert(Int, @hex)

    Above statement throws an error. I also tried to first convert hex string to varbinary, that also throws an error.

    Is there a built in function is SQL 2005?

    Any other idea or resolution, please suggest???????

  • There is no built in function - you would need to create your own.

    See http://blogs.msdn.com/rextang/archive/2008/01/13/7091118.aspx

  • Here is a script posted to SSC by Ofer Bester - all you need do, is a bit of copy and paste and you have what you want

    http://www.sqlservercentral.com/scripts/Miscellaneous/30094/

    I have used it and it works very well

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Because there is a general revulsion for row by agonizing row,

    a person could use a tally table and a values table rather than a while loop to accomplish the same task.

    Here is some code you can copy and paste to test with.

    You should probably make these tables real tables not temp tables.

    The first one is the reference for the value of each valid hex character.

    CREATE TABLE #HexConvertTbl (Val TINYINT, Hex CHAR(1) PRIMARY KEY )

    INSERT #HexConvertTbl ( Val, Hex )

    SELECT 0, '0'

    UNION SELECT 1, '1'

    UNION SELECT 2, '2'

    UNION SELECT 3, '3'

    UNION SELECT 4, '4'

    UNION SELECT 5, '5'

    UNION SELECT 6, '6'

    UNION SELECT 7, '7'

    UNION SELECT 8, '8'

    UNION SELECT 9, '9'

    UNION SELECT 10, 'A'

    UNION SELECT 11, 'B'

    UNION SELECT 12, 'C'

    UNION SELECT 13, 'D'

    UNION SELECT 14, 'E'

    UNION SELECT 15, 'F'

    --I hope you already have a Tally Table, just in case, here is a short one.

    --The tally table is for position counting

    CREATE TABLE #TallyTBL (N INT PRIMARY KEY)

    INSERT #TallyTBL ( N )

    SELECT 1

    UNION SELECT 2

    UNION SELECT 3

    UNION SELECT 4

    UNION SELECT 5

    UNION SELECT 6

    UNION SELECT 7

    UNION SELECT 8

    UNION SELECT 9

    UNION SELECT 10

    UNION SELECT 11

    UNION SELECT 12

    UNION SELECT 13

    UNION SELECT 14

    UNION SELECT 15

    UNION SELECT 16

    UNION SELECT 17

    UNION SELECT 18

    UNION SELECT 19

    UNION SELECT 20

    --Ideally these would be parameters of a function

    DECLARE @Hex varchar(16), @Len TINYINT

    --Sample data, hopefully this gets passed in as a parameter

    --You might have to clean up 0xFFFFFF format, but that should be easy enough

    SELECT @Hex = 'F270AF'

    --Limit for the number of rows the tally table split allows

    SELECT @Len = LEN(@Hex)

    --Summarize the values of each column

    SELECT SUM(Q1.ColumnVal) as DecimalVal

    FROM (

    --This is where the real work is done. For column values discussion see: http://www.codemastershawn.com/library/tutorial/hex.bin.numbers.php

    --For Tally table split reference, see: http://www.sqlservercentral.com/articles/T-SQL/62867/

    SELECT SUBSTRING(@hex,N,1) Hex, h.Val, POWER(16,@Len-N)*h.Val AS ColumnVal

    FROM #TallyTBL t

    JOIN #HexConvertTbl h

    ON h.Hex = SUBSTRING(@hex,N,1)

    WHERE N <= @Len

    ) AS Q1

    DROP TABLE #HexConvertTbl

    DROP TABLE #TallyTBL

    Does someone know how to use a Tally Table method to account for signed hex values?

    Our process is in place for values that will always be greater than 0.

  • I adapted the following from this link (https://social.msdn.microsoft.com/Forums/en-US/20e92e10-a0ab-4a53-a766-76f84bfd4e8c/converting-hex-values-to-int?forum=transactsql).

    It makes use of a "dummy" XML CAST so as to be able to use ".value".

    I CAST the resulting hex from the string column value to INT since I needed to do this, but you can easily remove that part if desired.

    (substitute your column name for col1, and of course supply your table/view after the final FROM):

    SELECT

    CAST

    (

    (

    SELECT

    CAST ('' AS XML).value('xs:hexBinary(substring(sql:column("col1"), sql:column("t.pos")) )', 'varbinary(max)')

    FROM

    (

    SELECT

    CASE SUBSTRING (col1, 1, 2)

    WHEN '0x'

    THEN 3

    ELSE 0

    END

    )

    AS t(pos)

    )

    AS INT

    )

    FROM


    Have Fun!
    Ronzo

  • Check if you can find this function in master database on SQL2005.

    But this works on SQL 2008:

    Declare @hex VARCHAR(50)

    Select @hex = '0x' + '009FF000' -- I added missing zero in front of the string

    SELECT Convert(Int, 0x09FF000) , CONVERT(Int, substring([sys].[fn_cdc_hexstrtobin](@hex), 1,4)), [sys].[fn_cdc_hexstrtobin](@hex)

    _____________
    Code for TallyGenerator

  • For the sake of fun:

    You might wish to convert to other integer data types, not only int.

    Try to use different types for @tgt in this script:

    Declare @hex VARCHAR(50), @tgt TINYINT, @typeLength TINYINT

    SET @tgt = 0

    SELECT @typeLength = DATALENGTH(@tgt)

    Select @hex = 'ff' --9FF000' --Retrieve hex string from a table

    Select @hex = '0x' + REPLICATE('0', @typeLength*2-LEN(LTRIM(@hex))) + LTRIM(@hex) --Retrieve hex string from a table

    set @tgt = substring([sys].[fn_cdc_hexstrtobin](@hex), 1, @typeLength)

    SELECT @tgt, [sys].[fn_cdc_hexstrtobin](@hex)

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

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