CONV() – Convert Numbers Between Different Bases in MySQL

When working with MySQL, you can use the CONV() function to convert a number from one base to another. It takes three arguments; the number to convert, the original base, and the base you want to convert it to.

Syntax

Here’s the official syntax:

CONV(N,from_base,to_base)

Where from_base is the original base, and to_base is the base you want to convert the number into.

Example – Decimal to Binary

Here’s an example of converting a number from base 10 (decimal) to base 2 (binary):

SELECT CONV(3, 10, 2);

Result:

+----------------+
| CONV(3, 10, 2) |
+----------------+
| 11             |
+----------------+

So we can see that 3 in decimal converts to 11 in binary.

In the case of binary, MySQL also has a BIN() function that allows you to convert from decimal to binary. Therefore, the above example is the equivalent of the following:

SELECT BIN(3);

Result:

+--------+
| BIN(3) |
+--------+
| 11     |
+--------+

Example – Binary to Decimal

However, one benefit of the CONV() function is that it also enables you to convert back the other way. So we could switch the above example to convert from binary to decimal:

SELECT CONV(11, 2, 10);

Result:

+-----------------+
| CONV(11, 2, 10) |
+-----------------+
| 3               |
+-----------------+

Example – Decimal to Hexadecimal

In this example we convert from decimal to hexadecimal:

SELECT CONV(13, 10, 16);

Result:

+------------------+
| CONV(13, 10, 16) |
+------------------+
| D                |
+------------------+

As demonstrated here, 13 in base 10 (decimal) converts into D in base 16 (hexadecimal).

Here’s another example, this time using a bigger number:

SELECT CONV(12734, 10, 16);

Result:

+---------------------+
| CONV(12734, 10, 16) |
+---------------------+
| 31BE                |
+---------------------+

Similar to the BIN() function for binary conversions, MySQL also has a HEX() function that converts a number from decimal to hexadecimal. So the previous example could be rewritten as follows:

SELECT HEX(12734);

Result:

+------------+
| HEX(12734) |
+------------+
| 31BE       |
+------------+

Example – Base 36

The CONV() function accepts a minimum base of 2 (binary) and a maximum base of 36. Here’s an example of converting from base 10 to base 36:

SELECT CONV(12734, 10, 36);

Result:

+---------------------+
| CONV(12734, 10, 36) |
+---------------------+
| 9TQ                 |
+---------------------+

2 Ways to Convert between Decimal and Hexadecimal in MySQL

This article presents two methods for converting a decimal number to its hexadecimal equivalent in MySQL.

More specifically, I present two functions that enable you to do this conversion. The two functions are as follows:

• The HEX() function
• The CONV() function

Below is an explanation of each function.

The HEX() Function

The HEX() function actually works on both numbers and strings. Here’s the syntax for using this function with numbers:

HEX(N)

Where N is the number you want to convert to hexadecimal.

Example

SELECT HEX(108);

Result:

+----------+
| HEX(108) |
+----------+
| 6C       |
+----------+

So 108 in decimal is 6C in hexadecimal. We can also get the same result by using the CONV() function.

The CONV() Function

The CONV() function has a more widespread use, in that it can be used to convert between any number of numeric systems (as long as they’re no lower than base 2 and no higher than base 36).

The syntax goes like this:

CONV(N,from_base,to_base)

Where N is the number you want to convert, from_base is the base you want to convert from, and to_base is the base you want to convert to.

Example

Therefore, we can rewrite the previous example to this:

SELECT CONV(108, 10, 16);

Result:

+-------------------+
| CONV(108, 10, 16) |
+-------------------+
| 6C                |
+-------------------+

So we can see by the code that we’re converting 108 from base 10 (decimal) to base 16 (hexadecimal).