MySQL - HEX() Function



MySQL HEX() function accepts a decimal number or a string value and returns the hexadecimal representation of it. Hexadecimal literal values in MySQL are represented in the form of X'val' or 0xval notation, where 'val' holds hexadecimal digits (0..9, A..F).

By default, a hexadecimal literal is a binary string, where each pair of hexadecimal digits represents a character. Therefore, if the argument passed to this function is a string, then each byte of each character in the string is converted to two hexadecimal digits.

But if the argument is a numeric value, its hexadecimal representation is treated as a BIGINT UNSIGNED.

Syntax

Following is the syntax of MySQL HEX() function −

HEX(val);

Parameters

This function takes a numeric or a string value as a parameter.

Return Value

This function returns the hexadecimal representation of the given input as a string.

Example

The following query uses the MySQL HEX() function to convert the decimal value 225 into its hexadecimal representation −

SELECT HEX(225) As Result;

Output

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

Result
E1

Example

Following is another example of this function, here we are converting the decimal value 215456 into its hexadecimal representation −

SELECT HEX(215456) As Result;

Output

This will produce the following result −

Result
349A0

Example

We can also pass a string value to this function −

SELECT HEX('tutorialspoint') As Result;

Output

The output is produced as follows −

Result
7475746F7269616C73706F696E74

Example

In the following query, we are trying to pass an integer as a string value to this function −

SELECT HEX('447353') As Result;

Output

This will produce the following result −

Result
343437333533

Example

You can also convert the values of a columns into hexa-decimal using the HEX() function. To do so, let us create a table named CUSTOMERS using the below query −

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 below query adds 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 );

To verify whether the records are inserted, execute the following query −

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

Following MySQL statement converts the values of the SALARY column into hexa-decimal −

Select ID, NAME, ADDRESS, HEX(SALARY) From CUSTOMERS;

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

ID NAME ADDRESS HEX(SALARY)
1 Ramesh Ahmedabad 7D0
2 Khilan Delhi 5DC
3 Kaushik Kota 7D0
4 Chaitali Mumbai 1964
5 Hardik Bhopal 2134
6 Komal Hyderabad 1194
7 Muffy Indore 2710
Advertisements