6

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

user1636130
  • 1,615
  • 5
  • 29
  • 47
  • That seems to be for converting an int into a hex format like #0xFF4B0082. I need a string format that I can append a '#' to the start of like '#4B0082' – user1636130 Jun 21 '13 at 13:48

2 Answers2

10

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)
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Many thanks for your help! I can't notice any difference between the two options here. In the end I used RIGHT( , 6) in order to trim the '0x' that these functions return a the start of the string and it worked! Thanks again x – user1636130 Jun 21 '13 at 13:57
  • 1
    The value returned by this function begins with `0x`, so you need `RIGHT (... , 6)` around `sys.fn_varbintohexstr` to make look as `#FF00FF`. – Stoleg Jun 21 '13 at 14:02
  • (Realizing this answer is over four years old --) Why not use [use `CONVERT`](https://www.sqlservercentral.com/Forums/1708816/Replacement-for-using-fnvarbintohexstr#bm1708994)? `SELECT CONVERT(VARCHAR(8),CONVERT(VARBINARY(8), 987654321),2)`? – ruffin Oct 22 '17 at 23:43
  • 1
    @ruffin: that syntax was only added in SQL Server 2008. Compare https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx vs https://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx "Binary Styles". OP was using SQL Server 2008, but I probably got mixed up in what version is which – gbn Oct 23 '17 at 06:05
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

Rolf
  • 730
  • 6
  • 14