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 −

Result
100101

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 −

Result
10011110

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 −

Result
NULL

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 −

4156456 * 56445
127

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 −

Result
NULL

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 −

ID NAME AGE ADDRESS SALARY
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

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 −

NAME AGE ADDRESS SALARY CONV(SALARY, 10, 2)
Ramesh 32 Ahmedabad 2000.00 11111010000
Khilan 25 Delhi 1500.00 10111011100
Kaushik 23 Kota 2000.00 11111010000
Chaitali 25 Mumbai 6500.00 1100101100100
Hardik 27 Bhopal 8500.00 10000100110100
Komal 22 Hyderabad 4500.00 1000110010100
Muffy 24 Indore 10000.00 10011100010000
Advertisements