Redshift SPLIT_PART in Snowflake, Databricks and BigQuery
Snowflake and Databricks keep SPLIT_PART with the same arguments, and both also accept negative positions counted from the end. BigQuery has no SPLIT_PART; index the array from SPLIT() with SAFE_OFFSET.
Redshift function: SPLIT_PART(string, delimiter, position) · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | SPLIT_PART(string, delimiter, partNumber) | Negative numbers count from the end; out-of-range positions return an empty string. | Snowflake docs |
| Databricks | Same name | split_part(str, delim, partNum) | Databricks SQL and Databricks Runtime 11.3 LTS and above; negative numbers count from the end, out of range returns an empty string. | Databricks docs |
| BigQuery | Rewrite | SPLIT(value, delimiter)[SAFE_OFFSET(n - 1)] | Zero-based offset; out-of-range positions return NULL rather than an empty string. | BigQuery docs |
Behavior differences to check
BigQuery
- Redshift returns '' for a missing part, BigQuery's SAFE_OFFSET returns NULL. Wrap the result in IFNULL(..., '') where downstream code compares with ''.
Examples
Domain from an email address
SELECT SPLIT_PART(email, '@', 2) AS domain FROM users;SELECT SPLIT_PART(email, '@', 2) AS domain FROM users;SELECT split_part(email, '@', 2) AS domain FROM users;SELECT SPLIT(email, '@')[SAFE_OFFSET(1)] AS domain FROM users;