SQL Query 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 will not work with 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?

Answer 1

You don't have a standard way (i.e. ANSI) of doing this conversion.

Anything you do will be proprietary functionality.

I'd suggest moving this conversion to your code instead of depending on the DB.

Answer 2

Convert INT to hex:

SELECT CONVERT(VARBINARY(8), 16777215)

Convert hex to INT:

SELECT CONVERT(INT, 0xFFFFFF)

See http://classicasp.aspfaq.com/general/how-do-i-convert-from-hex-to-int-and-back.html

Answer 3

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

Answer 4

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

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