Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: Convert an integer into a hex string?

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

like image 506
user1636130 Avatar asked Jun 21 '13 13:06

user1636130


People also ask

Can we convert int to string in SQL?

Using GUI. Then we can use the GUI to change the data type of the table from an int into a string. In SSMS, we can use the Design option to change the data type, by right clicking on the table name. In the designer, change the data type of OrderQty from an int into nvarchar(50) and save the changes.

What is a hex value in SQL?

HEX() : This function in MySQL is used to return an equivalent hexadecimal string value of a string or numeric Input. If the input is a string then each byte of each character in the string is converted to two hexadecimal digits.

How can I convert a hex string to an integer value?

To convert a hexadecimal string to a numberUse the ToInt32(String, Int32) method to convert the number expressed in base-16 to an integer. The first argument of the ToInt32(String, Int32) method is the string to convert. The second argument describes what base the number is expressed in; hexadecimal is base 16.

How do I save a hexadecimal value in SQL Server?

You can use varbinary type of column to store the hexa values. You can cast/convert them to integer as and when required using the cast/convert functions.


2 Answers

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)
like image 192
gbn Avatar answered Oct 12 '22 16:10

gbn


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

like image 42
Rolf Avatar answered Oct 12 '22 16:10

Rolf