Categories:: String & binary functions (Encoding/Decoding)

TRY_HEX_DECODE_BINARY¶

A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.

Syntax¶

TRY_HEX_DECODE_BINARY(<input>)

Arguments¶

input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.

Returns¶

A BINARY value that can, for example, be inserted into a column of type BINARY.

Examples¶

This shows how to use the function TRY_HEX_DECODE_BINARY (note that the function is used in the INSERT statement to decode into a BINARY field; the function is not used in the SELECT statement):

Create a table and data:

CREATE TABLE hex (v VARCHAR, b BINARY);
INSERT INTO hex (v, b)
   SELECT 'ABab', 
     -- Convert string -> hex-encoded string -> binary.
     TRY_HEX_DECODE_BINARY(HEX_ENCODE('ABab'));

Now run a query to show that we can retrieve the data intact:

SELECT v, b, 
    -- Convert binary -> hex-encoded-string -> string.
    TRY_HEX_DECODE_STRING(TO_VARCHAR(b)) 
  FROM hex;

Output:

+------+----------+--------------------------------------+
| V    | B        | TRY_HEX_DECODE_STRING(TO_VARCHAR(B)) |
|------+----------+--------------------------------------|
| ABab | 41426162 | ABab                                 |
+------+----------+--------------------------------------+