SQL Server STRING_SPLIT in Snowflake, Databricks and BigQuery
STRING_SPLIT is a table-valued function used with CROSS APPLY. Snowflake has SPLIT_TO_TABLE, Databricks explodes the array from split(), and BigQuery unnests the array from SPLIT().
SQL Server function: STRING_SPLIT(string, separator [, enable_ordinal]) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | LATERAL SPLIT_TO_TABLE(string, delimiter) | Returns SEQ, INDEX and VALUE columns; INDEX is the position of the element. | Snowflake docs |
| Databricks | Rewrite | LATERAL VIEW explode(split(str, regex)) | split() treats the delimiter as a regular expression, so characters such as | or . must be escaped. | Databricks docs |
| BigQuery | Rewrite | CROSS JOIN UNNEST(SPLIT(value, delimiter)) | SPLIT returns an ARRAY; UNNEST WITH OFFSET gives the position. | BigQuery docs |
Behavior differences to check
- STRING_SPLIT does not guarantee output order unless enable_ordinal = 1 (SQL Server 2022). Use the position column of the target when order matters.
Databricks
- split(tags, '|') splits on every character because | is regex alternation; write split(tags, '\\|') instead.
Examples
One row per tag
SELECT p.id, s.value AS tag FROM products p CROSS APPLY STRING_SPLIT(p.tags, ',') s;SELECT p.id, s.value AS tag FROM products p, LATERAL SPLIT_TO_TABLE(p.tags, ',') s;SELECT p.id, tag FROM products p LATERAL VIEW explode(split(p.tags, ',')) t AS tag;SELECT p.id, tag FROM products p CROSS JOIN UNNEST(SPLIT(p.tags, ',')) AS tag;