h3_validate function (Databricks SQL)

Returns the input value, that is of type BIGINT or STRING, if it corresponds to a valid H3 cell ID, or emits an error otherwise. In the case of STRING input, any leading zeros are trimmed.

Requires: SQL warehouse version 2022.35 or higher. This version is available in the Preview channel.

Syntax

h3_validate ( h3CellIdExpr )

Arguments

  • h3CellIdExpr: A BIGINT expression, or a STRING expression that is expected to represent a valid H3 cell ID.

Returns

A value of the same type as the type of the h3CellIdExpr expression, and equal to the value of the input h3CellIdExpr expression. In the case of STRING input, any leading zeros are trimmed.

The function returns NULL if the input is NULL.

Error conditions

Examples

-- Simple example taking a long as input.
> SELECT h3_validate(590112357393367039)
  590112357393367039

-- Simple example taking a hexadecimal string as input.
> SELECT h3_validate('85283473fffffff')
  85283473fffffff

-- Example taking a long as input that is not a valid H3 cell ID.
> SELECT h3_validate(599686042433355776)
  [H3_INVALID_CELL_ID] 599686042433355776 is not a valid H3 cell ID

-- Example where the input is an invalid hexadecimal string.
> SELECT h3_validate('I am not an H3 cell id')
  [H3_INVALID_CELL_ID] 'I am not an H3 cell id' is not a valid H3 cell ID