FROM_HEX

Byte Function.

Description

Converts a hexadecimal-encoded input string_expr into BYTES format.

For more information, please refer to FROM_HEX function.

The function FROM_HEX is translated to TRY_HEX_DECODE_BINARY in Snowflake.

Grammar Syntax

FROM_HEX(string)

Sample Source

BigQuery

Query

SELECT FROM_HEX('01020304');

Output

AQIDBA==

Snowflake

Query

SELECT TRY_HEX_DECODE_BINARY('01020304');

Output

AQIDBA==

Please keep in mind that the default output format for binary data types in BigQuery is 'BASE64' and in Snowflake 'HEX'. You can use the BASE64_ENCODE function or set the BINARY_OUTPUT_FORMAT format if you want to view the data in BASE64 format.

Using FROM_HEX function to insert binary data

These functions are not allowed in the values clause in Snowflake. For this reason, the values clause is transformed into a subquery.

BigQuery

CREATE OR REPLACE TABLE bytesTable
(
  COL1 BYTES,
  COL2 BYTES(20)
);

INSERT INTO bytesTable
VALUES (FROM_HEX('01020304'), FROM_HEX('AABBCCDD'));

Snowflake

CREATE OR REPLACE TABLE bytesTable
(
  COL1 BINARY /*** MSC-WARNING - MSCEWI1036 - BYTES DATA TYPE CONVERTED TO BINARY ***/,
  COL2 BINARY(20) /*** MSC-WARNING - MSCEWI1036 - BYTES DATA TYPE CONVERTED TO BINARY ***/
);

INSERT INTO bytesTable
SELECT
  TRY_HEX_DECODE_BINARY('01020304'),
  TRY_HEX_DECODE_BINARY('AABBCCDD');