MySQL HEX() function

In this context, we will learn how we can use the MySQL HEX() function with proper syntax and good examples.

Introduction of MySQL HEX() function

For returning an equivalent hexadecimal string value of a string or numeric Input, the HEX() function is used in MySQL. Each byte of each character in the string will be converted into two hexadecimal digits when the Input is a string. A hexadecimal string representation of the numeric argument N is also returned by this function which is treated as a longlong (BIGINT) number.

Syntax of the MySQL HEX() function

The syntax of the MySQL HEX() function is given as follows:

HEX(str)
OR
HEX(N)

Parameters or arguments used in MySQL HEX() function:

There is only one parameter accepted by the HEX() function in MySQL, which is given as follows:

Str – Input string whose each character is to be converted to two hexadecimal digits.

N – Input number, which is to be converted to hexadecimal.

Returns:

It will return an equivalent hexadecimal string representation of a string or numeric Input.

Application used for HEX() function:

The HEX() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL HEX() function:

Now we will look into some MySQL HEX() function examples and will explore how we can use the HEX function in MySQL.

Example-1:

In this example, we will derive the Hexadecimal representation of the decimal number 0 using the HEX Function in MySQL as follows.

SELECT HEX(0) AS Hex_number ;

Output :

HEX_NUMBER
0
1 row in set (0.00 sec)

Example-2 :

Here, we will derive the Hexadecimal representation of the decimal number 2020 using the HEX Function in MySQL as follows.

SELECT HEX( 2020 ) AS Hex_number ;

Output :

HEX_NUMBER
7E4
1 row in set (0.00 sec)

Example -3 :

Here, we will derive the Hexadecimal representation of the string 'javaTpoint' using the HEX Function in MySQL as follows.

SELECT HEX( 'javaTpoint') AS Hex_string ;

Output :

HEX_STRING
6A61766154706F696E74
1 row in set (0.00 sec)

Example-4 :

Here, we will use the HEX Function to derive a hexadecimal representation of all decimal numbers in a column.

Creating a Cricketer table:-

CREATE TABLE Cricketer(


Cricketer_id INT AUTO_INCREMENT,
Cricketer_name VARCHAR(100) NOT NULL,
Playing_team VARCHAR(20) NOT NULL,
Highest_Run_Scored INT NOT NULL,
PRIMARY KEY(Cricketer_id )
);

Now we will insert the data into the Table.

INSERT INTO
Cricketer(Cricketer_name, Playing_team, Highest_Run_Scored)
VALUES
('Virat Kohli,' 'RCB,' 60 ),
('Rohit Sharma,' 'MI,' 45),
('Dinesh Karthik', 'KKR', 26 ),
('Shreyash Iyer,' 'DC,' 40 ),
('David Warner', 'SRH', 65),
('Steve Smith,' 'RR,' 52 ),
('Andre Russell', 'KKR', 70),
('Jasprit Bumrah', 'MI', 10),
('Risabh Panth,' 'DC,' 34 ) ;

For verification, we will use the following command as follows.

SELECT * FROM Cricketer;

Output:

CRICKETER_ID	CRICKETER_NAME	PLAYING_TEAM	HIGHEST_RUN_SCORED
1	Virat Kohli	RCB	60
2	Rohit Sharma	MI	45
3	Dinesh Karthik	KKR	26
4	Shreyash Iyer	DC	40
5	David Warner	SRH	65
6	Steve Smith	RR	52
7	Andre Russell	KKR	70
8	Jasprit Bumrah	MI	10
9	Risabh Panth	DC	34

Now, we will find the highest run scored by each Cricketer in hexadecimal using the HEX Function.

SELECT
Cricketer_id, Cricketer_name,
Playing_team, HEX(HIGHEST_RUN_SCORED) AS HighestRunInHexaDecimal
FROM Cricketer ;

Output:

CRICKETER_ID	CRICKETER_NAME	PLAYING_TEAM	HighestRunInHexaDecimal
1	Virat Kohli	RCB	3C
2	Rohit Sharma	MI	2D
3	Dinesh Karthik	KKR	1A
4	Shreyash Iyer	DC	28
5	David Warner	SRH	41
6	Steve Smith	RR	34
7	Andre Russell	KKR	46
8	Jasprit Bumrah	MI	A
9	Risabh Panth	DC	22

Application of MySQL HEX() function:

This function is used to return an equivalent hexadecimal string value of a string or numeric Input.

Summary:

In the above context, we have learned how we can use the HEX() function in MySQL used to return an equivalent hexadecimal string value of a string or numeric Input.