SQL: Convert an integer into a hex string?

Question

How do you convert an integer into a string of hex? I want to convert the int into a format that I can use as a color on my page for example '#ff0000'.

So for example:

--This converts my int to hex:
CONVERT(VARBINARY(8), Color) Color,

And I want to do something like this:

'#' + CONVERT(NVARCHAR(10), CONVERT(VARBINARY(8), Color)) Color

But converting a varbinary string just converts it to an ascii character rather than returning the actual hex string

Solution 1

There is a built in function to generate hex strings from binary values

SELECT
    '#' + sys.fn_varbintohexstr(CONVERT(BINARY(3), 0)),
    '#' + sys.fn_varbintohexstr(CONVERT(BINARY(3), 255))

You need binary(3) to ensure the correct length of the output string
This is wrong. You get 4 hex digits because 0 and 255 here are 4 byte int values

SELECT
    '#' + sys.fn_varbintohexstr(CONVERT(varBINARY(8), 0)),
    '#' + sys.fn_varbintohexstr(CONVERT(varBINARY(8), 255))

Oct 2017 Update:

The conversion is now built-in to SQL Server (since 2008!!) so we can simply use CONVERT

SELECT '#' + CONVERT(char(6), CONVERT(BINARY(3), 2570841), 2)

Solution 2

Conversion from int to varbinary is implicit in SQL SERVER, so you can also use SELECT sys.fn_varbintohexstr(1234567) and you're done.

But beware BIGINT values, because long numeric literals are interpreted as DECIMAL values and not as BIGINT. Decimals have prefix data to hold the precision, and the byte order is reversed, and that's why you get this:

select sys.fn_varbintohexstr(2147483648)

returns 0x0A00000100000080

You need to convert explicitly to BIGINT:

select select sys.fn_varbintohexstr(CONVERT(BIGINT(2147483648))

returns 0x0000000080000000