How the UNHEX() function works in Mariadb?

The UNHEX() function in MariaDB is used to convert a hexadecimal string representation of a value into a string or binary value. It takes a hexadecimal string as input and returns the corresponding string or binary value.

Syntax

The syntax for the MariaDB UNHEX() function is as follows:

UNHEX(hex_string)

• hex_string: The input hexadecimal string to be converted.

The function returns the string or binary value represented by the hexadecimal string. If the input is not a valid hexadecimal string, it returns NULL.

Examples

Example 1: Converting a Simple Hexadecimal String to a String

This example demonstrates how to use the UNHEX() function to convert a simple hexadecimal string to a string.

SELECT UNHEX('48656C6C6F') AS string_value;

Output:

+--------------+
| string_value |
+--------------+
| Hello        |
+--------------+

The UNHEX() function converted the hexadecimal string ‘48656C6C6F’ to the string ‘Hello’.

Example 2: Converting a Hexadecimal String to a Binary Value

This example shows how to use the UNHEX() function to convert a hexadecimal string to a binary value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (data BLOB);
INSERT INTO example VALUES (UNHEX('68656C6C6F'));

SELECT HEX(data) AS hex_value, data AS binary_value FROM example;

Output:

+------------+--------------+
| hex_value  | binary_value |
+------------+--------------+
| 68656C6C6F | hello        |
+------------+--------------+

The UNHEX() function converted the hexadecimal string ‘68656C6C6F’ to the binary value ‘hello’, which was then stored in the data column of the example table.

Example 3: Converting a Longer Hexadecimal String

This example demonstrates how the UNHEX() function handles converting a longer hexadecimal string.

SELECT UNHEX('54686973206973206120686578616465636964616C20737472696E67') AS string_value;

Output:

+------------------------------+
| string_value                 |
+------------------------------+
| This is a hexadecidal string |
+------------------------------+

The UNHEX() function correctly converted the longer hexadecimal string to the corresponding string value.

Example 4: Handling an Invalid Hexadecimal String

This example shows the behavior of the UNHEX() function when provided with an invalid hexadecimal string.

SELECT UNHEX('Hello') AS string_value;

Output:

+--------------+
| string_value |
+--------------+
| NULL         |
+--------------+

When an invalid hexadecimal string is provided as input to the UNHEX() function, it returns NULL because the input is not a valid hexadecimal representation.

Example 5: Combining UNHEX() with Other Functions

This example demonstrates how to combine the UNHEX() function with other string functions.

SELECT CONCAT('String value: ', UNHEX('48656C6C6F')) AS result;

Output:

+---------------------+
| result              |
+---------------------+
| String value: Hello |
+---------------------+

In this example, the CONCAT() function is used to concatenate a prefix string with the string value obtained by applying the UNHEX() function to a hexadecimal string.

Related Functions

The following are a few functions related to the MariaDB UNHEX() function:

• MariaDB HEX() function is used to convert a string or binary value to a hexadecimal string representation.
• MariaDB BIN() function is used to convert a number to a binary string representation.
• MariaDB CONV() function is used to convert a value between different number bases.

Conclusion

The UNHEX() function in MariaDB is a useful tool for converting hexadecimal string representations into string or binary values. By understanding the syntax and usage examples, you can effectively incorporate this function into your SQL queries and data manipulation tasks. Whether you need to convert hexadecimal strings to readable text or binary data, the UNHEX() function provides a convenient solution for working with hexadecimal representations in your database.