MySQL - CONV() Function

The technique to represent and work with numbers is called number system. Decimal number system is the most common number system. Other popular number systems include binary number system, octal number system, hexadecimal number system, etc.

MySQL provides a set of functions to perform various numerical functions. The CONV() function of MYSQL is used to convert a given value from one number system to other and returns the result in the form of a string.

This function converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If from_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

Syntax

Following is the syntax of MySQL CONV() function −

CONV(N, from_base, to_base)

Parameters

This function accepts 3 parameters, they are −

• N − the number you need to convert.

• from_base − The current representation system of the given number.

• to_base − The representation system to which you need to convert the given number.

Return Value

This function returns the number converted to the specified output base as a string.

Example

Following is the example of CONV() function, here it converts 25 from hexadecimal system to binary −

SELECT CONV(25, 16, 2) As Result;

Output

This will produce the following result −

Example

You can also pass the number to be converted as a string value −

SELECT CONV('8E', 18, 2) As Result;

Output

The output for the query above is produced as given below −

Example

If any of the arguments passed is null this function returns a NULL value −

SELECT CONV('8E', 0, 2) As Result;

Output

This will produce the following result −

Example

If the argument from_base passed to this function, is a negative value the number (N) id considered as a signed number −

SELECT CONV('7F', -16, 10) As Result;

Output

The output for the query above is produced as given below −

Example

For the from_base and to_base values the minimum and maximum values are 2 and 36. If the given values are out of this range the CONV() function returns NULL −

SELECT CONV('7F', 0, 10)  As Result;

Output

This will produce the following result −

Example

You can also convert the values of a columns from one representation system to other using the CONV() function. To do so, we create a table named CUSTOMERS using the query below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the below query to fetch all the inserted records in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us use the CONV() function to convert the values of the SALARY column into binary −

Select NAME,AGE,ADDRESS,SALARY, CONV(SALARY, 10, 2) 
FROM CUSTOMERS;

The output for the query above is produced as given below −