Syntax error Working with hex numbers in MySQL?

Working with hex numbers in MySQL?

In order to work with hex, use the CONV() function to convert between bases. The syntax is as follows −

SET anyVariableName = CONV(yourHexValue,16,10);

To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −

mysql> DELIMITER //
mysql> CREATE PROCEDURE SP_HEX_TO_DEC( HEXVALUE VARCHAR(10) )
   -> BEGIN
   -> DECLARE Decimalvalue INTEGER;
   -> SET Decimalvalue = CONV(HEXVALUE,16,10);
   -> select Decimalvalue;
   -> END;
   -> //
Query OK, 0 rows affected (0.19 sec)
mysql> DELIMITER ;

The above stored procedure converts the hexadecimal to decimal. As we know the A represents 10 in decimal, we will pass A as the parameter. Call the stored procedure using CALL command.

The syntax is as follows −

CALL yourStoredProcedureName;

Call the above stored procedure using CALL command. The query is as follows −

mysql> call SP_HEX_TO_DEC('A');

The following is the output displaying the decimal value calculated using the stored procedure created above −

+--------------+
| Decimalvalue |
+--------------+
| 10           |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Check with select statement directly.

mysql> select conv('AB',16,10) as DecimalResult;

The following is the output −

+---------------+
| DecimalResult |
+---------------+
| 171           |
+---------------+
1 row in set (0.00 sec)

Let us now see the procedure to convert hex to decimal. Remember this rule −

A and B represented as 10 and 11 respectively in hexadecimal.
To convert it into decimal rule is as follows:
N ………+value3 *162 +value2 *161 + value1 * 160
= 10 * 161 + 11 * 160
= 160+11
= 171.
Updated on: 2020-06-30T08:02:12+05:30

468 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements