MySQL8 UNHEX() Functions – String Functions

by · Published · Updated

MySQL UNHEX() Functions: Syntax

UNHEX ( string );

MySQL UNHEX() Functions: Description

UNHEX() Functions for a string argument, return value is a binary string.

UNHEX() function for a string argument string, UNHEX(string) reads each pair of characters in the argument as a hexadecimal number and modify it to the byte represented by the number. The return value is a binary string. It will work fine for string inputs. See example 3.

The characters in the argument string must be valid hexadecimal digits i.e : ‘0….9’, ‘A….F’, ‘a …. f’. If the argument contains any non-hexadecimal digits, the result is NULL . See example 2.

If the argument is a numeric argument then, Hex() function returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to  CONV(HEX(N),16,10). The inverse of this operation is performed by CONV(HEX(N),16,10).

MySQL UNHEX() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|30%|55%” colalign=”left|left|left|left”]
Name, Required /Optional, Description
string, Required, It represents valid hex representation of string.
[/table]

MySQL UNHEX() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns, for a string argument it returns a binary string.
[/table]

MySQL UNHEX() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 5.7
[/table]

MySQL UNHEX() Functions: Example 1

mysql> SELECT HEX(200), CONV(HEX(200),16,10);
+----------+----------------------+
| HEX(200) | CONV(HEX(200),16,10) |
+----------+----------------------+
| C8       | 200                  |
+----------+----------------------+
1 row in set (0.04 sec)

MySQL UNHEX() Functions: Example 2

mysql> SELECT UNHEX('gg');
+-------------+
| UNHEX('gg') |
+-------------+
| NULL        |
+-------------+
1 row in set, 1 warning (0.05 sec)

MySQL UNHEX() Functions: Example 3

mysql>  SELECT X'74757473', HEX('tuts'), UNHEX(HEX('tuts'));
+-------------+-------------+--------------------+
| X'74757473' | HEX('tuts') | UNHEX(HEX('tuts')) |
+-------------+-------------+--------------------+
| tuts        | 74757473    | tuts               |
+-------------+-------------+--------------------+
1 row in set (0.00 sec)

MySQL UNHEX() Functions: Example 4

Shows numbers as input. As, u can see in this example, UNHEX() doesn’t inverse HEX() numeric values.

mysql> SELECT HEX(155), CONV(HEX(155),16,10) , unhex(hex(155));
+----------+----------------------+-----------------+
| HEX(155) | CONV(HEX(155),16,10) | unhex(hex(155)) |
+----------+----------------------+-----------------+
| 9B       | 155                  | �                |
+----------+----------------------+-----------------+
1 row in set (0.00 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : HEX(), CONCAT(), CONCAT_WS() , LOWER(), UPPER(), LTRIM(), RTRIM().

PHP Related articles : HEX2BIN(), BIN2HEX(), PHP STRING FUNCTIONS().

You may also like...