SQL SERVER – Question – How to Convert Hex to Decimal

In one of the recent projects, I realize the bottleneck of the query was an inline function which was converting Hex to Decimal. I optimized the inline function and reduced the query running time to one-tenth of the original running time. Later, I was eager to find out the script my blog readers might be using for hex to decimal conversion. Please leave your comments here and I will consider all the valid answers and publish with due credit to the author in one of the future posts. If the script you have posted here is not your original script, I suggest that you include the source as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Location of Resource Database in SQL Server Editions
Next Post
SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Fun

Related Posts

No results found

25 Comments. Leave new

  • Here is one way to do it:

    create function fn_HexToIntnt(@str varchar(16))
    returns bigint as begin

    select @str=upper(@str)
    declare @i int, @len int, @char char(1), @output bigint
    select @len=len(@str)
    ,@i=@len
    ,@output=case
    when @len>0
    then 0
    end
    while (@i>0)
    begin
    select @char=substring(@str,@i,1), @output=@output
    +(ASCII(@char)
    -(case
    when @char between ‘A’ and ‘F’
    then 55
    else
    case
    when @char between ‘0’ and ‘9’
    then 48 end
    end))
    *power(16.,@len-@i)
    ,@i=@i-1
    end
    return @output
    end

    Reply
    • Marcus Clive Macdonald
      March 24, 2016 3:42 pm

      thx, this is the only one that worked for me, for an RFID reader returning decimals from 8 hex characters others ways of doing it returned 10% negative and inconsistent values

      Reply
  • hmm..
    What about using built-in function Convert?

    SELECT CONVERT(INT, 0x00000100)
    SELECT CONVERT(VARBINARY(8), 256)

    I do not pretend to be the author, but i am using this for a long time

    Reply
  • Marko Parkkola
    February 1, 2010 4:56 pm

    I’ve lately interested in Sql Server’s CLR support so here goes.

    First I enable CLR support on Sql Server.

    EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’
    GO
    RECONFIGURE;
    GO

    Then I create a simple DLL assembly called DBClasses with a static class named StringFormatFunctions and a static method HexToInt.

    public partial class StringFormatFunctions
    {
    [SqlFunction(IsDeterministic = true)]
    public static int HexToInt(string input)
    {
    return int.Parse(input, System.Globalization.NumberStyles.HexNumber);
    }
    }

    Next I load it to Sql Server.

    CREATE ASSEMBLY DBClasses
    FROM ‘C:\path\DBClasses.dll’
    WITH PERMISSION_SET = SAFE

    Finally I create a little function for it.

    CREATE FUNCTION HexToInt(@input nvarchar)
    RETURNS int
    AS
    EXTERNAL NAME DBClasses.StringFormatFunctions.HexToInt

    And now I can start to use it.

    SELECT dbo.HexToInt(‘ffff’)

    Reply
  • If it is integer make use of implicit convertion

    SELECT CONVERT(VARBINARY(8), 659604),0x000A1094*1

    If it is decimal, use cast function

    SELECT CONVERT(VARBINARY(8), 659604.1),cast(0x07010001C9A56400 as decimal(12,2))

    Reply
  • If you are using SQL Server 2008, you can simply use the built-in function CONVERT, with the style 1:

    SELECT CONVERT(varchar(100),0x123456789abc123,1)+’?’
    SELECT CONVERT(varbinary,’0x0123456789ABC123′,1)+0x0

    Notice that on SQL Server 2005, the above queries will not yield an error, but you will get different results (the same as if we used style 0).

    Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Razvan

    Reply
  • Reply
  • James Curran
    March 8, 2010 9:46 pm

    OK, I’m going to assume that we are starting with a string holding hex digits, and we wnat to convert that into a int.

    declare @hex varchar(10);
    set @hex = ‘BD12’;

    declare @retval int;
    set @reval = — 48402 via some magic.

    Now, this being the wacky world of SQL, the best solution is gonna be something involving a set operation on tables, so let’s start with a permanaent table mapping hex digits to their values:

    CREATE TABLE [HexDigits](
    [Digit] [char](1) NOT NULL,
    [Value] [int] NOT NULL,
    CONSTRAINT [PK_HexDigits] PRIMARY KEY CLUSTERED ([Digit] ASC) )

    INSERT INTO [HexDigits] VALUES (‘0’, 0);
    INSERT INTO [HexDigits] VALUES (‘1’, 1);
    — etc
    INSERT INTO [HexDigits] VALUES (‘E’, 14);
    INSERT INTO [HexDigits] VALUES (‘F’, 15);

    Then, given a similar (presumably temporary) table mapping digits in the string we want translated to their digit position (count from the right), such as this,

    CREATE TABLE [HexString](
    [Digit] [char](1) NOT NULL,
    [Pos] [int] NOT NULL
    CONSTRAINT [PK_HexString] PRIMARY KEY CLUSTERED ([Digit] ASC) )
    INSERT INTO [HexString] VALUES (‘B’, 4);
    INSERT INTO [HexString] VALUES (‘D’, 3);
    INSERT INTO [HexString] VALUES (‘1’, 2);
    INSERT INTO [HexString] VALUES (‘2’, 1);

    Then we have the simple query

    select SUM(POWER(16,(pos-1))*Value)
    From HexString s, HexDigits d
    where s.Digit = d.Digit

    The trick here will be the convert the original string (“BD12”, in this example), into the rows if the HexString table. I don’t know of a good way to do that, but I’m sure someone knows a simple way to do that.

    Reply
  • Vedran Kesegic
    April 13, 2010 1:19 pm

    There is no ‘hex’ data type in sql server that we would convert from or to. We have hex literal (eg. 0xFFFF) which is int data type. We can have hex digits stored as ascii characters in varchar (e.g. ‘FFFF’, bytes 70,70,70,70 ), or as binary digits in varbinary (bytes 255, 255). You did not specify which sql type did you really want to convert from. If you want to convert from hex string to integer number, here it is:

    DECLARE @hexstr VARCHAR(10); SET @hexstr = ‘ffff’
    DECLARE @rez BIGINT; SET @rez = 0
    WHILE @hexstr ”
    BEGIN
    SET @rez = @rez * 16 + CHARINDEX(LEFT(@hexstr,1),’0123456789ABCDEF’) – 1
    SET @hexstr = SUBSTRING(@hexstr,2,100)
    END
    PRINT @rez

    Without loop, in one SELECT statement:

    DECLARE @hexstr VARCHAR(10); SET @hexstr = ‘ffff’
    DECLARE @rez BIGINT;
    SELECT @rez = ISNULL(@rez,0) * 16 + CHARINDEX(substring(@hexstr,n.number+1,1),’0123456789ABCDEF’) – 1
    FROM MASTER..spt_values n WHERE n.TYPE=’P’ AND n.number<len(@hexstr)
    PRINT @rez

    Vedran Kesegic

    Reply
    • beautiful code on the one without the loop! I couldn’t get the first code with the loop to work, but the second one is awesome! Thanks for depositing this here!

      Reply
  • Hi guys,
    Here’s an online tool I use to convert hex to decimal: hex to decimal converter
    Pretty cool!
    David

    Reply
  • Hi All,

    Please answer this,

    select convert(varbinary(2),unicode(N’B’))
    go
    declare @tmp_var varchar(10)
    SET @tmp_var = convert(varbinary(2),unicode(N’B’))
    select @tmp_var ‘tmp_var’

    Execute this query….

    When a varbinary value is assigned to a varchar type, then it shows NULL. Why???

    By
    Biju.K.S

    Reply
    • You can’t do that. Why do you want to do that?

      Reply
      • No practical use..!
        I just want to know that…

        Thanks for your reply..:)

      • Hi,

        please run this,

        select convert(varbinary(2),unicode(N’B’))
        go
        declare @tmp_var varchar(10)
        SET @tmp_var = sys.fn_varbintohexstr(convert(varbinary(2),unicode(N’B’)))
        select @tmp_var ‘tmp_var’

        ;)

        Thanks,
        Biju

  • Hi,

    please run this,

    select convert(varbinary(2),unicode(N’B’))
    go
    declare @tmp_var varchar(10)
    SET @tmp_var = sys.fn_varbintohexstr(convert(varbinary(2),unicode(N’B’)))
    select @tmp_var ‘tmp_var’

    ;)

    Thanks,
    Biju

    Reply
  • A Little late to the party, however when reading books online for convert I noticed you could apply styles when converting to binary.

    SELECT CONVERT(VARBINARY(8), 65535),CAST(0x0000FFFF AS INT),
    — If the value is a string without the 0x
    CAST(CONVERT(VARBINARY, ‘ffff’, 2) AS INT),
    CAST(CONVERT(VARBINARY, ‘0000ffff’, 2) AS INT),
    — or if your string has the 0x
    CAST(CONVERT(VARBINARY, ‘0x0000FFFF’, 1) AS INT),
    CAST(CONVERT(VARBINARY, ‘0xFFFF’, 1) AS INT)

    0x0000FFFF,65535,65535,65535,65535,65535

    Reply
  • building on Ray’s contribution above, here is the generic case:

    declare @hex varchar(64) = ‘0x00FF’
    select cast( CONVERT(VARBINARY,’0x’+right(‘00000000’+replace(@hex,’x’,”),8),1) as int)

    This could easily be made into a function.

    Reply
  • Moderator, please remove the “commented” values after the declare statement above. The two comment dashes got replaced by another symbol. Thanks

    Reply
  • aspdotnetcsharpsntosh
    March 9, 2012 7:26 pm

    hi.
    i want to convert hex to readable string how to do pls help me its very urgent.

    i have hex like “0605040B8423F0660601AE02056A00”
    want to convert in human readable string.

    pls help …..

    Reply
  • Here is another solution

    SELECT CONVERT(int, CONVERT(varbinary, ‘0xFF’, 1)) returns 255

    so replace ‘0xFF’ with any other hex value and try. Remember to always include ‘0x’ in the beginning.

    Reply
  • today your website posts have helped me lots of times to resolve problem from my manager.
    you’re the best!!
    thank you

    Reply
  • CREATE TABLE #t
    (
    ip varchar(200)
    )

    insert into #t
    select ‘C0A8019A’
    union all
    select ‘0A0B0028’
    union all
    select ‘0A0B2531’
    union all
    select ‘0A0B62CF’
    union all
    select ‘0A0B415F’
    union all
    select ‘0A0B62CF’
    union all
    select ‘0A0B2531’
    union all
    select ‘0A0B62CF’
    union all
    select ‘0A0B2531’
    union all
    select ‘0A0B62CF’
    union all
    select ‘0A0B415F’
    union all
    select ‘0A0B81B0′

    select ip,substring(ip,1,2) A1
    ,substring(ip,len(substring(ip,1,2))+1,2) B1
    ,substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2) C1
    ,substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2) D1

    ,case when left(substring(ip,1,2),1) =’A’ Then 10 * 16
    when left(substring(ip,1,2),1) =’B’ Then 11 * 16
    when left(substring(ip,1,2),1) =’C’ Then 12 * 16
    when left(substring(ip,1,2),1) =’D’ Then 13 * 16
    when left(substring(ip,1,2),1) =’E’ Then 14 * 16
    when left(substring(ip,1,2),1) =’F’ Then 15 * 16
    ELSE left(substring(ip,1,2),1) * 16
    END
    +
    case when Right(substring(ip,1,2),1) =’A’ Then 10
    when Right(substring(ip,1,2),1) =’B’ Then 11
    when Right(substring(ip,1,2),1) =’C’ Then 12
    when Right(substring(ip,1,2),1) =’D’ Then 13
    when Right(substring(ip,1,2),1) =’E’ Then 14
    when Right(substring(ip,1,2),1) =’F’ Then 15
    ELSE Right(substring(ip,1,2),1)
    END AS A

    ,case when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’A’ Then 10 * 16
    when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’B’ Then 11 * 16
    when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’C’ Then 12 * 16
    when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’D’ Then 13 * 16
    when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’E’ Then 14 * 16
    when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’F’ Then 15 * 16
    ELSE left(substring(ip,len(substring(ip,1,2))+1,2),1) * 16
    END
    +
    case when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’A’ Then 10
    when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’B’ Then 11
    when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’C’ Then 12
    when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’D’ Then 13
    when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’E’ Then 14
    when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’F’ Then 15
    ELSE Right(substring(ip,len(substring(ip,1,2))+1,2),1)
    END AS B

    ,case when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’A’ Then 10 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’B’ Then 11 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’C’ Then 12 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’D’ Then 13 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’E’ Then 14 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’F’ Then 15 * 16
    ELSE left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) * 16
    END
    +
    case when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’A’ Then 10
    when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’B’ Then 11
    when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’C’ Then 12
    when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’D’ Then 13
    when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’E’ Then 14
    when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’F’ Then 15
    ELSE Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1)
    END AS C

    ,case when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’A’ Then 10 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’B’ Then 11 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’C’ Then 12 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’D’ Then 13 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’E’ Then 14 * 16
    when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’F’ Then 15 * 16
    ELSE left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) * 16
    END
    +
    case when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’A’ Then 10
    when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’B’ Then 11
    when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’C’ Then 12
    when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’D’ Then 13
    when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’E’ Then 14
    when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’F’ Then 15
    ELSE Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1)
    END AS D
    INTO #temp
    from #t

    select ip, convert(varchar,A)+’.’+ convert(varchar,B)+’.’+convert(varchar,C) +’.’+convert(varchar,D) IPAddress from #temp

    Reply
  • Jayesh Gangrade
    October 12, 2012 3:41 pm

    Hi pinal,

    please give me solution of following error

    The TCP/IP connection to the host 11.01.0.45, port 1433 has failed. Error: “Address already in use: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.
    1000
    com.websym.common.exception.DBException: org.hibernate.exception.JDBCConnectionException: Cannot open connection

    Jayesh G

    Reply
  • I have a solution using a tally table and a hex table (hex table idea from James Curran’s suggestion above) that handles strings both with and without a leading ‘0x’, and that is very fast.

    For those who don’t know, a tally table is simply a table with one column filled with sequential numbers. Here is a simple way to create and load a tally table with powers of 2 numbers:

    —————-
    CREATE TABLE tally (n int)
    DECLARE @i int,
    @maxN int

    SET @i = 16

    INSERT INTO tally(n) VALUES(1)

    WHILE @i > 1
    BEGIN
    SELECT @maxN = MAX(n)
    FROM tally

    INSERT INTO tally(n)
    SELECT n + @maxN
    FROM tally

    SET @i = @i – 1
    END
    —————-

    This builds a 32,768 entry tally table starting at one. Now we can use the tally table to simplify building the hex table, as well as in speeding the decoding of a hex string.

    ——————
    CREATE TABLE hexDigits (
    h char(1) NOT NULL PRIMARY KEY CLUSTERED,
    v int NOT NULL
    )
    GO

    DECLARE @s varchar(15)
    SET @s = ‘123456789ABCDEF’

    INSERT INTO hexDigits VALUES(‘0’, 0)

    INSERT INTO hexDigits(h,v)
    SELECT SUBSTRING(@s, t.n, 1), t.n
    FROM tally AS t
    WHERE t.n BETWEEN 1 and LEN(@s)
    —————-

    Now we can decode the hex string quickly with this function:

    —————-

    CREATE FUNCTION fnHex2Int (@s varchar(18))
    RETURNS bigint
    AS
    BEGIN
    DECLARE @ret bigint

    SELECT
    @s = REPLACE(SUBSTRING(@s + ‘.’, PATINDEX(‘%[^0xX]%’, @s + ‘.’), 18), ‘.’, 0)
    IF @s NOT LIKE ‘%[^0-9A-Fa-f]%’
    BEGIN
    SELECT @ret = SUM(POWER(16,(num.n – 1)) * h.v)
    FROM hexdigits AS h
    RIGHT JOIN (
    SELECT SUBSTRING(REVERSE(@s), t.n, 1) AS x,
    t.n
    FROM tally AS t
    WHERE t.n BETWEEN 1 and LEN(@s)
    ) num
    ON h.h = num.x
    END
    RETURN @ret
    END

    —————-

    Now, you can test the function. This query:

    SELECT dbo.fnHex2Int(‘0x00001000’) as h1,
    dbo.fnHex2Int(‘00001000’) as h2,
    dbo.fnHex2Int(‘0x00000000’) as h3,
    dbo.fnHex2Int(‘0xray100’) as h4

    Returned:

    h1 h2 h3 h4
    65536 65536 0 NULL

    Reply

Leave a Reply

Menu