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

Input Code:

BigQuery

IN -> BigQuery_01.sql

SELECT FROM_HEX('01020304');

Output Code:

Snowflake

OUT -> BigQuery_01.sql

SELECT TRY_HEX_DECODE_BINARY('01020304');

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.

Input Code:

BigQuery

IN -> BigQuery_02.sql

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

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

Output Code:

Snowflake

OUT -> BigQuery_02.sql

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

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