Categories:

String & Binary Functions (Encoding/Decoding)

HEX_DECODE_BINARY

Decodes a hex-encoded string to a binary.

See also:

TRY_HEX_DECODE_BINARY

Syntax

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

Start with a string; encode it as characters representing hexadecimal digits; then convert those hex digit characters to BINARY using HEX_DECODE_BINARY:

Create and fill a table:

CREATE TABLE binary_table (v VARCHAR, b BINARY);
INSERT INTO binary_table (v, b) 
    SELECT 'HELLO', HEX_DECODE_BINARY(HEX_ENCODE('HELLO'));

Now retrieve the BINARY value and display it as the original string (in the 3rd column of the output):

SELECT v, b, HEX_DECODE_STRING(TO_VARCHAR(b)) FROM binary_table;
+-------+------------+----------------------------------+
| V     | B          | HEX_DECODE_STRING(TO_VARCHAR(B)) |
|-------+------------+----------------------------------|
| HELLO | 48454C4C4F | HELLO                            |
+-------+------------+----------------------------------+

Decode a hex-encoded binary (output by MD5_BINARY):

SELECT HEX_DECODE_BINARY(HEX_ENCODE(MD5_BINARY('Snowflake')));

--------------------------------------------------------+
 HEX_DECODE_BINARY(HEX_ENCODE(MD5_BINARY('SNOWFLAKE'))) |
--------------------------------------------------------+
 EDF1439075A83A447FB8B630DDC9C8DE                       |
--------------------------------------------------------+