What is MySQL HEX() function and how it is different from CONV() function?n

Actually, HEX() function converts the decimal or string value into a hexadecimal value. After conversion MySQL returns a string representation of that hexadecimal value.

Syntax

HEX(Num or Str)

As we know that HEX() function can convert a number or string hence ‘Num’ in the syntax represents the number which is to be converted to hexadecimal and ‘Str’ is a string whose character is to be converted to two hexadecimal digits.

Example

mysql> Select HEX(210);
+----------+
| HEX(210) |
+----------+
| D2       |
+----------+
1 row in set (0.00 sec)

In the above example, 210 is a decimal number which is converted to a hexadecimal string representation and it is treated as a BIGINT number.

mysql> SELECT HEX('NULL');
+-------------+
| HEX('NULL') |
+-------------+
| 4E554C4C    |
+-------------+
1 row in set (0.00 sec)

In the above example, ‘NULL’ is a string whose characters are converted to two hexadecimal digits (per character two hexadecimal digits).

Basically, MySQL HEX() function is equivalent to CONV(N,10,16) but the basic difference is that HEX() can convert string character to two hexadecimal digits but CONV() returns 0 on trying to convert string characters into a hexadecimal string. It is demonstrated in the following example −

Example

mysql> Select HEX('N');
+----------+
| HEX('N') |
+----------+
| 4E       |
+----------+
1 row in set (0.00 sec)

mysql> Select CONV('N',10,16);
+-----------------+
| CONV('N',10,16) |
+-----------------+
| 0               |
+-----------------+
1 row in set (0.00 sec)