Oracle REGEXP_SUBSTR in Snowflake, Databricks and BigQuery
Snowflake REGEXP_SUBSTR takes the same position, occurrence and group arguments as Oracle. BigQuery REGEXP_SUBSTR is a synonym of REGEXP_EXTRACT. Databricks regexp_substr returns the whole match only; use regexp_extract for a capture group.
Oracle function: REGEXP_SUBSTR(source, pattern [, position [, occurrence [, match_param [, subexpr]]]]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | REGEXP_SUBSTR(subject, pattern [, position [, occurrence [, regex_parameters [, group_num]]]]) | Same argument order as Oracle. | Snowflake docs |
| Databricks | Same name | regexp_substr(str, regexp) | Two arguments only and the whole match. For a group use regexp_extract(str, regexp, idx). | Databricks docs |
| BigQuery | Same name | REGEXP_SUBSTR(value, regexp [, position [, occurrence]]) | Synonym of REGEXP_EXTRACT; with one capture group it returns the group. | BigQuery docs |
Behavior differences to check
Databricks
- REGEXP_SUBSTR(s, 'x=(\\d+)', 1, 1, NULL, 1) becomes regexp_extract(s, 'x=(\\d+)', 1).
BigQuery
- If the pattern has one capture group, BigQuery returns the group rather than the whole match, which differs from Oracle without the subexpr argument.
Examples
Domain from an email address
SELECT REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain FROM users;SELECT REGEXP_SUBSTR(email, '@(.+)$', 1, 1, 'e', 1) AS domain FROM users;SELECT regexp_extract(email, '@(.+)$', 1) AS domain FROM users;SELECT REGEXP_SUBSTR(email, r'@(.+)$') AS domain FROM users;