H3_STRING_TO_CELL

Parse an H3 cell string in canonical 15 hex-digit form into its BIGINT cell index.

Category: h3Returns: BIGINTDialect: Standard

Syntax

H3_STRING_TO_CELL(str)

Description

## Overview Parses an H3 cell from its canonical 15 hex-digit string representation into the BIGINT cell index. Accepts optional leading whitespace and an optional '0x' or '0X' prefix. The parser validates that the resulting 64-bit value is actually a real H3 cell; strings that are hex-parseable but do not encode a valid cell return NULL. This function is the inverse of H3_CELL_TO_STRING. Use it when ingesting H3 data from APIs, JSON, URLs, or other sources that store cell IDs as strings. Because the function validates the result, it is safe to use as both a parser and a validator: a non-NULL return is guaranteed to be a valid cell. ## Behavior - Returns a BIGINT containing the H3 cell index. - Returns NULL if the string is NULL, is empty, contains non-hex characters, or does not encode a valid H3 cell. - Strips leading whitespace and an optional '0x' or '0X' prefix before parsing. - Parsing is case-insensitive for hex digits. - If the parse succeeds but the resulting BIGINT is not a valid H3 cell (per H3_IS_VALID_CELL), the function returns NULL. That is, this function combines parsing with validation in one step. - Deterministic: the same input string always returns the same result. - Canonical string form is 15 lowercase hex digits; shorter hex strings can parse if they correspond to low-numbered base cells, but the canonical form is what H3_CELL_TO_STRING emits. ## Compatibility - Follows the standard H3 hierarchical hex grid specification for string-to-cell parsing. Strings produced by any compliant H3 library parse here correctly.

Parameters

Name	Type	Description
`str`		Specifies the H3 cell as a hexadecimal string (for example '892830926dfffff'). Leading whitespace and an optional '0x' or '0X' prefix are stripped. The remaining digits must parse as a valid H3 cell.

Examples

-- A real resolution 9 cell; returns the BIGINT index.
SELECT H3_STRING_TO_CELL('892830926dfffff') AS cell_id;

SELECT
  c AS original,
  H3_STRING_TO_CELL(H3_CELL_TO_STRING(c)) AS round_trip,
  c = H3_STRING_TO_CELL(H3_CELL_TO_STRING(c)) AS equal
FROM (SELECT H3_LATLNG_TO_CELL(51.5074, -0.1278, 9) AS c);

-- Convert external string cells to BIGINT for efficient storage and joins.
SELECT h3_str,
       H3_STRING_TO_CELL(h3_str) AS h3_cell,
       H3_GET_RESOLUTION(H3_STRING_TO_CELL(h3_str)) AS resolution
FROM imports.raw.h3_string_events
WHERE H3_STRING_TO_CELL(h3_str) IS NOT NULL;

-- Leading 0x or 0X is stripped; mixed-case hex is parsed case-insensitively.
SELECT H3_STRING_TO_CELL('0x892830926dfffff') AS cell_id;

-- Non-hex characters or strings that do not represent a real H3 cell return NULL.
SELECT
  H3_STRING_TO_CELL('not-hex')         AS bad_chars,
  H3_STRING_TO_CELL('deadbeef')        AS not_a_cell,
  H3_STRING_TO_CELL('892830926dfffff') AS ok_cell;

Pitfalls

The function validates as well as parses. A hex string that parses to a BIGINT but does not represent a real H3 cell returns NULL, not a garbage cell. This is usually the desired behavior; if you want raw parsing, use CAST(CONV(str, 16, 10) AS BIGINT) instead.
The function is case-insensitive for hex digits but produces the canonical lowercase form on round-trip via H3_CELL_TO_STRING. Do not assume input case is preserved anywhere.
Strings longer than 16 hex digits overflow a uint64 and return NULL. Strings of fewer than 15 digits can still be valid H3 cells if the leading digits are implicit zeros.
Leading '0x'/'0X' is tolerated but the canonical form never emits it. For interchange, pick one convention (typically no prefix) and enforce it in your ingest.
When parsing many rows, combine with H3_IS_VALID_CELL on the result for clarity, even though it is redundant. It documents the validation expectation in the query.