Here are 3 ways to convert a hexadecimal value to an integer in SQL Server.

First up are two T-SQL functions that allow you to convert a hex value to an integer. In addition to these functions, there’s also the concept of implicit conversion, which can also produce the same result.

Example 1 – The CONVERT() Function

The first function we’ll look at is the CONVERT() function. This allows you to convert between data types in SQL Server.

Here’s an example of using this function to convert a hexadecimal value to an integer:

SELECT CONVERT(INT, 0xfcab) Result;

Result:

+----------+
| Result   |
|----------|
| 64683    |
+----------+

In this case, we convert the hexadecimal value fcab (prefixed with 0x) to an integer.

Example 2 – The CAST() Function

We can alternatively use the CAST() function to do the same thing as the previous example:

SELECT CAST(0xfcab AS INT) Result;

Result:

+----------+
| Result   |
|----------|
| 64683    |
+----------+

Note that CAST() and CONVERT() use slightly different syntaxes. In the case of CAST() the value to be cast comes first, whereas it’s the other way around with CONVERT().

Example 3 – Implicit Conversion

Here’s an example of performing an implicit conversion, simply by multiplying the hex value by 1:

SELECT 0xfcab * 1 Result;

Result:

+----------+
| Result   |
|----------|
| 64683    |
+----------+

Example 4 – Formatting the Result

We can also use the FORMAT() function to format the end result. In this case, we still use a conversion function to perform the actual conversion. It’s just that we also use the FORMAT() function to format the result as a number (by using the N format specifier).

SELECT FORMAT(CAST(0xfcab AS INT), 'N') Result;

Result:

+-----------+
| Result    |
|-----------|
| 64,683.00 |
+-----------+

Note that this function actually returns the result as a string.

Also note that the FORMAT() function only accepts numeric and date and time data types. Therefore, we can’t pass a hexadecimal value directly to the function. If we try to do that, here’s what happens:

SELECT FORMAT(0xfcab, 'N') Result;

Result:

Argument data type varbinary is invalid for argument 1 of format function.