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.

Syntax

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 valid hexadecimal string Azure Databricks returns an CONVERSION_INVALID_INPUT error. Use try_to_binary to return a NULL value instead.

  • 'BASE64'

    expr must be a RFC 4648 §4: base64 (standard) encoded string.

    The result is the decoded binary data. Under RFC 4648 §4 any malformed bae64 string will result in a CONVERSION_INVALID_INPUT error.

  • '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