How to convert the number into different number base system using CONV() in MySQL8?
With MySQL, we can do complex calculations very easily with the help of inbuilt mathematical functions. CONV(X) works in converting a number from one number system to another number system. Hence, making conversion of the number from Decimal to binary number system and vice versa and etc, more easy job.
MySQL CONV() : Syntax
CONV(number) works in converting a user-entered number from one number base system to another number base system. It takes 3 arguments i.e. input number X, base_from is the original base and base_to is the base in which we want to convert the number into.
It returns a string representation of a number X, converted from base base_from to base base_to.
It returns NULL, if any of the input arguments value is NULL.
The argument number is interpreted as an integer, but it can be specified as an integer or a string.
The minimum base is 2 i.e. Binary and the maximum base is 36 i.e. hexadecimal.
If base_from is a negative number, X is regarded as a signed number. Otherwise, X is treated as unsigned.
CONV() works with 64-bit precision.
MySQL CONV() : Parameter
Name, Required /Optional, Description
X, Required, It represents a valid number.
base_from, Required, Existing base of the number X.
base_to, Required, Base of the number X after conversion.
MySQL CONV() : Output
Return, Description
NULL, if the argument is NULL.
string representation of a number X, converted from base_from to base_to.
MySQL CONV() Available from : MySQL 4.0
CONV() Example 1 : Decimal to Binary
Below is an example of converting a number from base 10 (decimal) to base 2 (binary):
mysql> SELECT CONV(4, 10, 2); +----------------+ | CONV(4, 10, 2) | +----------------+ | 100 | +----------------+ 1 row in set (0.00 sec)
You can see that 4 in decimal converts to 100 in binary.
For converting decimal into binary in MySQL, there is one more function i.e. BIN(), which can do the same thing.
So, the above example is similar to the following:
mysql> SELECT BIN(4); +--------+ | BIN(4) | +--------+ | 100 | +--------+ 1 row in set (0.00 sec)
CONV() Example 2 : Binary to Decimal
CONV() also provide us the feature of converting the binary value back to a decimal value. Let’s take the above example convert the binary value 100 to decimal value 4.
Below is the way to do this.
mysql> SELECT CONV(100, 2, 10); +------------------+ | CONV(100, 2, 10) | +------------------+ | 4 | +------------------+ 1 row in set (0.00 sec)
CONV() Example 3 : Decimal to Hexadecimal
Below is an example of converting a number from base 10 (decimal) to base 16 (Hexadecimal). Below is the way to do this.
mysql> SELECT CONV(12789, 10, 16); +---------------------+ | CONV(12789, 10, 16) | +---------------------+ | 31F5 | +---------------------+ 1 row in set (0.00 sec)
For converting decimal into binary in MySQL, there is one more function i.e. HEX(), which can do the same thing. So, the above example is similar to the following:
mysql> SELECT HEX(12789); +------------+ | HEX(12789) | +------------+ | 31F5 | +------------+ 1 row in set (0.10 sec)
CONV() Example 4 : Hexadecimal to Decimal
CONV() also provide us the feature of converting the Hexadecimal value back to a decimal value. Let’s take the above example convert the Hexadecimal value ’31F5′ to decimal value 12789.
Below is the way to do this.
mysql> SELECT CONV('31F5', 16, 10);
+----------------------+
| CONV('31F5', 16, 10) |
+----------------------+
| 12789 |
+----------------------+
1 row in set (0.01 sec)
CONV() Example 5 : Decimal to Octal
Below is an example of converting a number from base 10 (decimal) to base 8 (Octal):
mysql> SELECT CONV(14, 10, 8); +-----------------+ | CONV(14, 10, 8) | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec)
You can see that 14 in decimal converts to 16 in Octal.
For converting decimal into binary in MySQL, there is one more function i.e. OCT(), which can do the same thing.
So, the above example is similar to the following:
mysql> SELECT OCT(14); +---------+ | OCT(14) | +---------+ | 16 | +---------+ 1 row in set (0.03 sec)
CONV() Example 6 : Octal to Decimal
CONV() also provide us the feature of converting the binary value back to an Octal value. Let’s take the above example convert the octal value 16 to decimal value 14.
Below is the way to do this.
mysql> SELECT CONV(16, 8, 10); +-----------------+ | CONV(16, 8, 10) | +-----------------+ | 14 | +-----------------+ 1 row in set (0.00 sec)
CONV() Example 7 : It will return Warning if any string argument is passed.
See the below example :
mysql> SELECT CONV('test', 2, 10);
+---------------------+
| CONV('test', 2, 10) |
+---------------------+
| 0 |
+---------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'test' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
CONV() Example 8 : NULL arguments
If any of the arguments is NULL, it will return NULL. See the below example :
mysql> SELECT CONV(NULL, 2, 10); +-------------------+ | CONV(NULL, 2, 10) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.06 sec) mysql> SELECT CONV(4, NULL, 2); +------------------+ | CONV(4, NULL, 2) | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec) mysql> SELECT CONV(4, 2, NULL); +------------------+ | CONV(4, 2, NULL) | +------------------+ | NULL | +------------------+ 1 row in set (0.01 sec)
CONV() Example 9 : Decimal to Base 36
Below is an example of converting a number from base 10 (decimal) to base 36. Below is the way to do this.
mysql> SELECT CONV(12789, 10, 36); +---------------------+ | CONV(12789, 10, 36) | +---------------------+ | 9V9 | +---------------------+ 1 row in set (0.00 sec)
CONV() Example 10 : Base 36 to Decimal
CONV() also provide us the feature of converting the Hexadecimal value back to a decimal value. Let’s take the above example convert the Hexadecimal value ‘9V9’ to decimal value 12789.
mysql> SELECT CONV('9V9', 36, 10);
+---------------------+
| CONV('9V9', 36, 10) |
+---------------------+
| 12789 |
+---------------------+
1 row in set (0.00 sec)
