MySQL CONV() [Explained With Easy Examples]

In this tutorial, we will study the MySQL CONV() function. Before you proceed with this article, it might be a good idea to brush up the concepts covered in the BIN(), OCT() and HEX() functions article.

The MySQL CONV() is a very powerful function when it comes to converting numbers from one base or radix to the other. In the article I referred to earlier, we saw how to convert a decimal number to its binary, octal and hexadecimal equivalent using the BIN(), OCT() and HEX() functions. But what if you want to do the reverse? Suppose you have a binary number and what to convert it to its decimal equivalent. How would you go about it? Furthermore, what if you want to convert a hexadecimal number to its base-4 equivalent? This is where the CONV() function comes into play. With the CONV() function, you can convert a number from a numeric base system to any numeric base system (2 to 32). It outputs the result as a string. Let us take a look at the syntax before we explore the examples of the CONV() function.

Syntax of MySQL CONV()

CONV(number, from_base, to_base)Code language: SQL (Structured Query Language) (sql)

Where,

• ‘number’ is a number which you want to convert from one numeric base system to the other.
• ‘from_base’ is the original base of the ‘number’. It should be a number 2 and 36.
• ‘to_base’  is the base to which you want to convert ‘number’ to. It should be a number 2 and 36 or -2 and -36.

Examples of MySQL CONV()

Let’s see some examples of the MySQL CONV() function.

MySQL CONV() Equivalent Of BIN()

Let us write the BIN() function’s equivalent using the MySQL CONV() function. We will convert the decimal numbers – 4 and 12, to their binary equivalent. The queries are – 

SELECT CONV(4, 10, 2) AS BinaryValue; 
SELECT CONV(12, 10, 2) AS BinaryValue;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL CONV() Equivalent Of OCT()

Now, let us write the OCT() function’s equivalent using the MySQL CONV() function. We will convert the decimal numbers – 15 and 26, to their octal equivalent. The queries are – 

SELECT CONV(15, 10, 8) AS OctalValue; 
SELECT CONV(26, 10, 8) AS OctalValue;Code language: SQL (Structured Query Language) (sql)

And the output is –

CONV() Equivalent of HEX()

Next, let us write the HEX() function’s equivalent using the CONV() function. We will convert the decimal numbers – 18 and 14, to their hexadecimal equivalent. The queries are –

SELECT CONV(18, 10, 16) AS HexadecimalValue; 
SELECT CONV(14, 10, 16) AS HexadecimalValue;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using CONV() to Convert From Binary to Decimal

Now, let us do the reverse of BIN(). Let us use CONV() to convert a binary number to its decimal equivalent. The queries are –

SELECT CONV(11110, 2, 10) AS BinToDecimal; 
SELECT CONV(1011100, 2, 10) AS BinToDecimal;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using CONV() to Convert From Octal to Decimal

Let us also do the reverse of OCT(). Let us use CONV() to convert an octal number to its decimal equivalent. The queries are –

SELECT CONV(10, 8, 10) AS OctalToDecimal; 
SELECT CONV(45, 8, 10) AS OctalToDecimal;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using CONV() to Convert From Hexadecimal to Decimal

Let us also do the reverse of HEX(). Let us use CONV() to convert a hexadecimal number to its decimal equivalent. The queries are –

SELECT CONV(‘D’, 16, 10) AS HexToDecimal; 
SELECT CONV(‘44F’, 16, 10) AS HexToDecimal;Code language: SQL (Structured Query Language) (sql)

Note that you need to pass hexadecimal numbers within single quotes.

And the output is –

Using CONV() to Convert From Hexadecimal to Binary

Since CONV() is so powerful and versatile, we can virtually convert any number from any numeric base system to another. Let us see an example of converting a hexadecimal number to its binary equivalent.

SELECT CONV(‘D’, 16, 2) AS HexToBinary;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using CONV() to Convert From Octal to Base-4

We can use any base from 2 to 36 with CONV(). Let us try converting an octal number to its base-4 equivalent.

SELECT CONV(12, 8, 4) AS OctalToBase4;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using CONV() to Convert FROM Base-4 to Binary

Lastly, let’s see an example of CONV() where we convert a number of Base-4 to its binary equivalent.

SELECT CONV(20, 4, 2) AS Base4ToBinary;Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below Integers table. The Number column contains numbers with the base-10 (decimal system).

Let us write a query that uses the SELECT statement and aliases to display the Number column and the Base-5, Base-15 and Base-32 equivalent of the values in it. The query is –

SELECT Number, CONV(Number, 10, 5) AS Base5, 
CONV(Number, 10, 15) AS Base15, 
CONV(Number, 10, 32) AS Base32 FROM Integers;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

Conclusion

Converting from one base representation to another is an important operation especially with data involving the electronics and digital circuits domain and MySQL CONV() is a very powerful function for that. I would encourage you to read more of these number systems and functions.