Convert integer to hex and hex to integer

Question

So I have this query working (where signal_data is a column) in Sybase but it doesn't work in Microsoft SQL Server:

HEXTOINT(SUBSTRING((INTTOHEX(signal_data)),5,2)) as Signal

I also have it in Excel (where A1 contains the value):

=HEX2DEC(LEFT(DEC2HEX(A1),LEN(DEC2HEX(A1))-2))

Does anyone know how I would do this in SQL Server?

Mina

Answer 1

Convert INT to hex:

SELECT CONVERT(VARBINARY(8), 16777215)

Convert hex to INT:

SELECT CONVERT(INT, 0xFFFFFF)

Update 2015-03-16

The above example has the limitation that it only works when the HEX value is given as an integer literal. For completeness, if the value to convert is a hexadecimal string (such as found in a varchar column) use:

-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

Note: The string must contain an even number of hex digits. An odd number of digits will yield an error.

More details can be found in the "Binary Styles" section of CAST and CONVERT (Transact-SQL). I believe SQL Server 2008 or later is required.

Ptomaine

Converting it to VARBINARY will give data in hex value . String operations cannot be performed on it – Womenfolk

@Bill Karwin, Pavan is correct. Sql Server 2008 can do this via the convert function but 2005 and below cannot. – Peptonize

When using 8-character Hex, such as with ARGB convert to BIGINT instead of INT. The max value for INT is only 2,147,483,647, or 7FFFFFFF. Beware when using INT, the round trip back to hex will work, but the results are different from Excel's Hex2Dec. – Fernandofernas

SELECT CONVERT(INT, CONVERT(varbinary, '01', 2)) returns 12337 (SQL Server 2005) – Bosnia

The first examples don't convert to hexadecimal: They convert to binary. (The binary value exists independently of any number base: A number base makes sense only in the context of a string representation.) (Also, the parameter doesn't have to be literal (though it does have to be an integer.)) They might seem like they are converting to hexadecimal because when converting binary to string (e.g. to display it), it represents it in hexadecimal by default. (This can be used to convert to hexadecimal, as shown in @KipBryan's answer, but DenNukem and wndproc's answers are simpler.) – Trinitrophenol

Answer 2

Actually, the built-in function is named master.dbo.fn_varbintohexstr.

So, for example:

SELECT 100, master.dbo.fn_varbintohexstr(100)

Gives you

100 0x00000064

Overspill

See dba.stackexchange.com/questions/46910/… on the cons. – Cockcrow

@Cockcrow Most of the 'cons' - aside from lack of documentation/specification - sound like BS!. – Rations

Answer 3

SQL Server equivalents to Excel's string-based DEC2HEX, HEX2DEC functions:

--Convert INT to hex string:
PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), 16777215),2) --DEC2HEX

--Convert hex string to INT:
PRINT CONVERT(INT,CONVERT(VARBINARY(4),'00FFFFFF',2)) --HEX2DEC

Guarantor

Answer 4

It is possible using the function FORMAT available on SQL Server 2012 and above

select FORMAT(10,'x2')

Results in:

0a

Zenas

Answer 5

Convert int to hex:

SELECT FORMAT(512+255,'X')

Zante

Since SQL Server 2012 only: msdn.microsoft.com/en-us/library/hh213505.aspx – Cockcrow

And the case of 'X' determines the case of hex output. up voted. – Forelock

Answer 6

The traditonal 4 bit hex is pretty direct. Hex String to Integer (Assuming value is stored in field called FHexString) :

CONVERT(BIGINT,CONVERT(varbinary(4),
                (SELECT master.dbo.fn_cdc_hexstrtobin(

                    LEFT(FMEID_ESN,8)                       
                ))
                ))

Integer to Hex String (Assuming value is stored in field called FInteger):

(SELECT master.dbo.fn_varbintohexstr(CONVERT(varbinary,CONVERT(int,
                    FInteger
                ))))

Important to note is that when you begin to use bit sizes that cause register sharing, especially on an intel machine, your High and Low and Left and Rights in the registers will be swapped due to the little endian nature of Intel. For example, when using a varbinary(3), we're talking about a 6 character Hex. In this case, your bits are paired as the following indexes from right to left "54,32,10". In an intel system, you would expect "76,54,32,10". Since you are only using 6 of the 8, you need to remember to do the swaps yourself. "76,54" will qualify as your left and "32,10" will qualify as your right. The comma separates your high and low. Intel swaps the high and lows, then the left and rights. So to do a conversion...sigh, you got to swap them yourselves for example, the following converts the first 6 of an 8 character hex:

(SELECT master.dbo.fn_replvarbintoint(
                CONVERT(varbinary(3),(SELECT master.dbo.fn_cdc_hexstrtobin(
                    --intel processors, registers are switched, so reverse them 


                    ----second half
                    RIGHT(FHex8,2)+ --0,1 (0 indexed)
                    LEFT(RIGHT(FHex8,4),2)+ -- 2,3 (oindex)
                    --first half
                    LEFT(RIGHT(FHex8,6),2) --4,5

                )))
                ))

It's a bit complicated, so I would try to keep my conversions to 8 character hex's (varbinary(4)).

In summary, this should answer your question. Comprehensively.

Bondstone

Answer 7

Here is the function for SQL server which converts integer value into its hexadecimal representation as a varchar. It should be easy to adapt to other database types

For example:

SELECT dbo.ToHex(4095) --> FFF

SQL:

CREATE FUNCTION ToHex(@value int)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @seq char(16)
    DECLARE @result varchar(50)
    DECLARE @digit char(1)
    SET @seq = '0123456789ABCDEF'

    SET @result = SUBSTRING(@seq, (@value%16)+1, 1)

    WHILE @value > 0
    BEGIN
        SET @digit = SUBSTRING(@seq, ((@value/16)%16)+1, 1)

        SET @value = @value/16
        IF @value <> 0 SET @result = @digit + @result
    END 

    RETURN @result
END
GO

Suneya

It is working! Perfect! You just have to replace "IF @ value 0" by "IF @ value <> 0" – Discipline

I like this solution because it's easily modifiable into an even more powerful one. I supplied such an example solution of using the length of the sequence as the base. – Ancillary

Answer 8

Use master.dbo.fnbintohexstr(16777215) to convert to a varchar representation.

Niece

select master.sys.fn_varbintohexstr(1) in 2008 – Arouse

Answer 9

Maksym Kozlenko has a nice solution, and others come close to unlocking it's full potential but then miss completely to realized that you can define any sequence of characters, and use it's length as the Base. Which is why I like this slightly modified version of his solution, because it can work for base 16, or base 17, and etc.

For example, what if you wanted letters and numbers, but don't like I's for looking like 1's and O's for looking like 0's. You can define any sequence this way. Below is a form of a "Base 36" that skips the I and O to create a "modified base 34". Un-comment the hex line instead to run as hex.

declare @value int = 1234567890

DECLARE @seq varchar(100) = '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ' -- modified base 34
--DECLARE @seq varchar(100) = '0123456789ABCDEF' -- hex
DECLARE @result varchar(50)
DECLARE @digit char(1)
DECLARE @baseSize int = len(@seq)
DECLARE @workingValue int = @value

SET @result = SUBSTRING(@seq, (@workingValue%@baseSize)+1, 1)

WHILE @workingValue > 0
BEGIN
    SET @digit = SUBSTRING(@seq, ((@workingValue/@baseSize)%@baseSize)+1, 1)

    SET @workingValue = @workingValue/@baseSize
    IF @workingValue <> 0 SET @result = @digit + @result
END 

select @value as Value, @baseSize as BaseSize, @result as Result

Value, BaseSize, Result

1234567890, 34, T5URAA

I also moved value over to a working value, and then work from the working value copy, as a personal preference.

Below is additional for reversing the transformation, for any sequence, with the base defined as the length of the sequence.

declare @value varchar(50) = 'T5URAA'

DECLARE @seq varchar(100) = '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ' -- modified base 34
--DECLARE @seq varchar(100) = '0123456789ABCDEF' -- hex
DECLARE @result int = 0
DECLARE @digit char(1)
DECLARE @baseSize int = len(@seq)
DECLARE @workingValue varchar(50) = @value

DECLARE @PositionMultiplier int = 1
DECLARE @digitPositionInSequence int = 0

WHILE len(@workingValue) > 0
BEGIN
    SET @digit = right(@workingValue,1)
    SET @digitPositionInSequence = CHARINDEX(@digit,@seq)
    SET @result = @result + ( (@digitPositionInSequence -1) * @PositionMultiplier)

    --select @digit, @digitPositionInSequence, @PositionMultiplier, @result

    SET @workingValue = left(@workingValue,len(@workingValue)-1)
    SET @PositionMultiplier = @PositionMultiplier * @baseSize
END 

select @value as Value, @baseSize as BaseSize, @result as Result

Ancillary

Answer 10

Declare @Dato xml
Set @Dato = Convert(xml, '<dato>FF</dato>')
Select Cast( rw.value( 'xs:hexBinary( text()[1])' , 'varbinary(max)' ) as int ) From @Dato.nodes('dato') as T(rw)

Tobacconist

Answer 11

The answer by Maksym Kozlenko is nice and can be slightly modified to handle encoding a numeric value to any code format. For example:

CREATE FUNCTION [dbo].[IntToAlpha](@Value int)
RETURNS varchar(30)
AS
BEGIN
    DECLARE @CodeChars varchar(100) 
    SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    DECLARE @CodeLength int = 26
    DECLARE @Result varchar(30) = ''
    DECLARE @Digit char(1)

    SET @Result = SUBSTRING(@CodeChars, (@Value % @CodeLength) + 1, 1)
    WHILE @Value > 0
    BEGIN
        SET @Digit = SUBSTRING(@CodeChars, ((@Value / @CodeLength) % @CodeLength) + 1, 1)
        SET @Value = @Value / @CodeLength
        IF @Value <> 0 SET @Result = @Digit + @Result
    END 

    RETURN @Result
END

So, a big number like 150 million, becomes only 6 characters (150,000,000 = "MQGJMU")

You could also use different characters in different sequences as an encrypting device. Or pass in the code characters and length of characters and use as a salting method for encrypting.

And the reverse:

CREATE FUNCTION [dbo].[AlphaToInt](@Value varchar(7))
RETURNS int
AS
BEGIN
    DECLARE @CodeChars varchar(100) 
    SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    DECLARE @CodeLength int = 26
    DECLARE @Digit char(1)
    DECLARE @Result int = 0
    DECLARE @DigitValue int
    DECLARE @Index int = 0
    DECLARE @Reverse varchar(7)
    SET @Reverse = REVERSE(@Value)

    WHILE @Index < LEN(@Value)
    BEGIN
        SET @Digit = SUBSTRING(@Reverse, @Index + 1, 1)
        SET @DigitValue = (CHARINDEX(@Digit, @CodeChars) - 1) * POWER(@CodeLength, @Index)
        SET @Result = @Result + @DigitValue
        SET @Index = @Index + 1
    END 
    RETURN @Result

Leviathan

Answer 12

Given:

declare @hexStr varchar(16), @intVal int

IntToHexStr:

select @hexStr = convert(varbinary, @intVal, 1)

HexStrToInt:

declare
    @query varchar(100),
    @parameters varchar(50)

select
    @query = 'select @result = convert(int,' + @hb + ')',
    @parameters = '@result int output'

exec master.dbo.Sp_executesql @query, @parameters, @intVal output

Grazynagreabe

Answer 13

Below are two functions: dbo.HexToInt and dbo.IntToHex, I use them for such conversion:

if OBJECT_ID('dbo.HexToInt') is not null
    drop function dbo.HexToInt
GO
create function dbo.HexToInt (@chars varchar(max))
returns int
begin
    declare @char varchar(1), @len int, @i int, @r int, @tmp int, @pow int
    set @chars = RTRIM(LTRIM(@chars))
    set @len = LEN(@chars)
    set @i = 1
    set @r = 0
    while @i <= @len
    begin
        set @pow = @len - @i
        set @char = SUBSTRING(@chars, @i, 1)
        if @char = '0'
            set @tmp = 0
        else if @char = '1'
            set @tmp = 1
        else if @char = '2'
            set @tmp = 2
        else if @char = '3'
            set @tmp = 3
        else if @char = '4'
            set @tmp = 4
        else if @char = '5'
            set @tmp = 5
        else if @char = '6'
            set @tmp = 6
        else if @char = '7'
            set @tmp = 7
        else if @char = '8'
            set @tmp = 8
        else if @char = '9'
            set @tmp = 9
        else if @char = 'A'
            set @tmp = 10
        else if @char = 'B'
            set @tmp = 11
        else if @char = 'C'
            set @tmp = 12
        else if @char = 'D'
            set @tmp = 13
        else if @char = 'E'
            set @tmp = 14
        else if @char = 'F'
            set @tmp = 15
        set @r = @r + @tmp * POWER(16,@pow)
        set @i = @i + 1     
    end
    return @r
end

And the second one:

if OBJECT_ID('dbo.IntToHex') is not null
    drop function dbo.IntToHex
GO
create function dbo.IntToHex (@val int)
returns varchar(max)
begin
    declare @r varchar(max), @tmp int, @v1 int, @v2 int, @char varchar(1)
    set @tmp = @val
    set @r = ''
    while 1=1
    begin
        set @v1 = @tmp / 16
        set @v2 = @tmp % 16
        if @v2 = 0
            set @char = '0'
        else if @v2 = 1
            set @char = '1'
        else if @v2 = 2
            set @char = '2'
        else if @v2 = 3
            set @char = '3'
        else if @v2 = 4
            set @char = '4'
        else if @v2 = 5
            set @char = '5'
        else if @v2 = 6
            set @char = '6'
        else if @v2 = 7
            set @char = '7'
        else if @v2 = 8
            set @char = '8'
        else if @v2 = 9
            set @char = '9'
        else if @v2 = 10
            set @char = 'A'
        else if @v2 = 11
            set @char = 'B'
        else if @v2 = 12
            set @char = 'C'
        else if @v2 = 13
            set @char = 'D'
        else if @v2 = 14
            set @char = 'E'
        else if @v2 = 15
            set @char = 'F'
        set @tmp = @v1 
        set @r = @char + @r
        if @tmp = 0
            break
    end
    return @r
end

Rushton

Answer 14

IIF(Fields!HIGHLIGHT_COLOUR.Value="","#FFFFFF","#" & hex(Fields!HIGHLIGHT_COLOUR.Value) & StrDup(6-LEN(hex(Fields!HIGHLIGHT_COLOUR.Value)),"0"))

Is working for me as an expression in font colour

Polemic

Answer 15

To convert Hex strings to INT, I have used this in the past. It can be modified to convert any base to INT in fact (Octal, Binary, whatever)

Declare @Str varchar(200)
Set @str = 'F000BE1A'

Declare @ndx int
Set @ndx = Len(@str)
Declare @RunningTotal  BigInt
Set @RunningTotal = 0

While @ndx > 0
Begin
    Declare @Exponent BigInt
    Set @Exponent = Len(@Str) - @ndx

    Set @RunningTotal = @RunningTotal + 

    Power(16 * 1.0, @Exponent) *
    Case Substring(@str, @ndx, 1)
        When '0' then 0
        When '1' then 1
        When '2' then 2 
        When '3' then 3
        When '4' then 4
        When '5' then 5
        When '6' then 6
        When '7' then 7
        When '8' then 8
        When '9' then 9
        When 'A' then 10
        When 'B' then 11
        When 'C' then 12
        When 'D' then 13
        When 'E' then 14
        When 'F' then 15
    End
    Set @ndx = @ndx - 1
End

Print @RunningTotal

Quinidine

Answer 16

In addition to DenNukem's answer:

You might need to type large numbers for the format function to work.

DECLARE @a bigint = 2147942405
SELECT 
    [typed] = FORMAT(@a,'X'), 
    [literal] = FORMAT(2147942405,'X')

/*
literal | typed
--------+----------
NULL    | 80070005
*/

Ricky

Answer 17

select to_hex(signal_data) as signal_data from table

Roundy