try_to_binary
function
Applies to: Databricks SQL preview Databricks Runtime 11.3 LTS and above
Returns expr
cast to BINARY based on fmt
, or NULL if the input is not valid.
Arguments
expr
: A STRING expression to cast.fmt
: A STRING literal describing how to interpretexpr
.
Notes
If fmt
is supplied, it must be one of (case-insensitive):
'HEX'
expr
must be a hexadecimal string. Each character must be a hexadecimal digit and there must be an even number of digits. The result is the binary representation of the hexadecimal string.If
expr
is not a well-formed hexadecimal value the function returnsNULL
. Use to_binary to return an error instead.'BASE64'
expr
must be a RFC 4648 §4: base64 (standard) encoded string. The result is the decoded binary data.'UTF-8'
or'UTF8'
expr
is interpreted as a UTF-8 string. The result is the binary representation of the string.
The default fmt
is 'HEX'
.
Examples
> SELECT cast(to_binary('537061726B') AS STRING);
Spark
> SELECT cast(to_binary('hello', 'hex') AS STRING);
Error: CONVERSION_INVALID_INPUT
> SELECT cast(try_to_binary('hello', 'hex') AS STRING);
NULL
> SELECT cast(to_binary('537061726B', 'hex') AS STRING);
Spark
> SELECT cast(to_binary('U3Bhcms=', 'base64') AS STRING);
Spark
> SELECT cast(to_binary('U3Bhxcms=', 'base64') AS STRING);
Error: CONVERSION_INVALID_INPUT
> SELECT cast(try_to_binary('U3Bhxcms=', 'base64') AS STRING);
NULL
> SELECT hex(to_binary('서울시(Seoul)', 'UTF-8'));
EC849CEC9AB8EC8B9C2853656F756C29