try_to_binary function

  • Article

Applies to: check marked yes Databricks SQL preview check marked yes Databricks Runtime 11.3 LTS and above

Returns expr cast to BINARY based on fmt, or NULL if the input is not valid.

Syntax

try_to_binary(expr [, fmt] )

Arguments

  • expr: A STRING expression to cast.
  • fmt: A STRING literal describing how to interpret expr.

Returns

A BINARY.

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 returns NULL. 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