How to Convert Integers and Hex Strings in SQL Server?

Converting Integers and Hex Strings in SQL Server

The provided Sybase and Excel queries aim to convert integers to hex and hex strings to integers. However, these queries may fail when executed in Microsoft SQL Server. Fortunately, there are straightforward methods to perform these conversions in SQL Server.

Converting Integer to Hex

To convert an integer (represented as int_value) to a hexadecimal string, use the following syntax:

SELECT CONVERT(VARBINARY(8), int_value)

This query will return an 8-byte hexadecimal string representation of the integer.

Converting Hex to Integer

To convert a hexadecimal string (represented as hex_string) to an integer, use the following syntax:

SELECT CONVERT(INT, hex_string)

This query will return the integer equivalent of the hexadecimal string.

Update

In 2015, an update was made to address the limitation where the above conversions only work with hexadecimal values provided as integer literals. The following examples demonstrate how to convert hexadecimal strings that may be stored in a varchar column:

With '0x' Marker:

SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

Without '0x' Marker:

SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

Note that the hexadecimal string must contain an even number of hex digits; an odd number of digits will result in an error.

For further details, consult the "Binary Styles" section of the CAST and CONVERT documentation for SQL Server. These methods should function properly in SQL Server 2008 or later versions.

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