How to Convert Between Integers and Hexadecimal Values in SQL Server?

Convert Integer to Hex and Hex to Integer in SQL Server

In SQL Server, there are two conversion functions that can be used to convert between integers and hexadecimal values:

Converting Integer to Hex

The CONVERT(VARBINARY, ) function can be used to convert an integer to its hexadecimal representation. For example:

SELECT CONVERT(VARBINARY(8), 16777215);

This will return the hexadecimal value 0xFFFFFF, which is the hexadecimal representation of the integer 16777215.

Converting Hex to Integer

The CONVERT(INT, ) function can be used to convert a hexadecimal string to its integer representation. For example:

SELECT CONVERT(INT, '0xFFFFFF');

This will return the integer 16777215, which is the integer representation of the hexadecimal string 0xFFFFFF.

Note: The hexadecimal string must contain an even number of hexadecimal digits. If it contains an odd number of digits, an error will be raised.

Update (2015-03-16)

The above example has the limitation that it only works when the HEX value is given as an integer literal. If the value to convert is a hexadecimal string (such as found in a varchar column), use the following:

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));

More details can be found in the "Binary Styles" section of CAST and CONVERT (Transact-SQL). This functionality is available in SQL Server 2008 or later.

The above is the detailed content of How to Convert Between Integers and Hexadecimal Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!