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?

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.

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