Tuesday 30 July 2019

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                 |
+---------------------+

0 comments:

Post a Comment