Convert integer to hex and hex to integer

sql

convert-function

hex-string

sql-server

byAlex Kataev·Nov 12, 2024

To convert an integer to hex, use the function CONVERT(VARCHAR(8), YourInteger, 2). For converting a hex to integer, use CONVERT(INT, YourHexNumber).

-- Int to Hex (replace 255 with your integer)
-- SQL Server: Making conversion easy since 1989! 💾
SELECT CONVERT(VARCHAR(8), 255, 2) AS HexValue;

-- Hex to Int (replace 'FF' with your hex string)
-- If SQL Server were a person, it would be Neo from the Matrix! 🕶️ 
SELECT CONVERT(INT, 0xFF) AS IntValue;

This quick answer assumes we are working with SQL Server 2008 or later. Other systems might have different methods for conversion.

Getting used to SQL's CONVERT quirks

Dealing with '0x' in Hex Strings

If your hexadecimal string starts with '0x', remember to use style 1 in your CONVERT function. SQL Server can be a little stubborn about these things!

-- Hex string with '0x' to Integer
-- Because SQL Server knows the value of 'x' ☝️
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1)) AS IntValue;

Handling Hex Strings without '0x'

When a 0x prefix is absent, use style 2. Keep in mind, SQL Server likes its digits in pairs, so pad with zeros accordingly. No one likes odd ones out!

-- Hex string without '0x' to Integer
-- When SQL Server says jump, you don't ask how high. You ask: in binary or hexadecimal? 🤔
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2)) AS IntValue;

Making pretty hex strings with FORMAT

From SQL Server 2012 and onward, life got a little easier because we now have the FORMAT function.

-- Int to Hex using FORMAT
-- Because everyone deserves a bit of formatting in their life 😎
SELECT FORMAT(255, 'X') AS HexValue; -- Returns 'FF'

Advanced methods and precautions

Use of the good old fn_varbintohexstr function

Alternatives do exist in some older systems; you might encounter the legacy function master.dbo.fn_varbintohexstr:

-- Using legacy function for Int to Hex
-- Bringing back the good old days ⌛
SELECT master.dbo.fn_varbintohexstr(255) AS HexValue;

Be mindful though, this function may not be around forever!

Understanding differences between SQL Server and Excel

Let's acknowledge that while Excel offers straightforward DEC2HEX and HEX2DEC functions, SQL Server requires a different approach.

Type your data right!

Use VARBINARY for hex representation and either VARCHAR or INT for decoded values, ensuring the data types play well in your usage context.

Beware of odd ones out

Make sure your hex strings always contain an even number of hex digits, or SQL Server might throw a tantrum.

explain-codes / Sql / Convert integer to hex and hex to integer

Linked

Convert hex string to integer in Python



Decoding T-SQL CAST in C#/VB.NET



Convert a string to int using SQL query



Imply bit with constant 1 or 0 in SQL Server



Cast from VARCHAR to INT - MySQL



What's the correct way to convert bytes to a hex string in Python 3?



Inserting text string with hex into PostgreSQL as a bytea



Getting used to SQL's CONVERT quirks Advanced methods and precautions