Teradata OREPLACE in Snowflake, Databricks and BigQuery
OREPLACE becomes REPLACE in all three targets. NULL handling differs: Teradata treats a NULL replacement as an empty string, while the targets return NULL.
Teradata function: OREPLACE(source_string, search_string [, replace_string]) · Teradata docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | REPLACE(subject, pattern [, replacement]) | The replacement defaults to an empty string. | Snowflake docs |
| Databricks | Different name | replace(str, search [, replace]) | The replacement defaults to an empty string. | Databricks docs |
| BigQuery | Different name | REPLACE(original_value, from_pattern, to_pattern) | All three arguments are required. | BigQuery docs |
Behavior differences to check
- OREPLACE(s, 'x', NULL) removes 'x' in Teradata; REPLACE(s, 'x', NULL) returns NULL in the targets. Pass '' explicitly.
BigQuery
- The two-argument form OREPLACE(s, 'x') must become REPLACE(s, 'x', '').
Examples
Remove dashes from phone numbers
SELECT OREPLACE(phone, '-') AS phone_digits FROM customers;SELECT REPLACE(phone, '-') AS phone_digits FROM customers;SELECT replace(phone, '-') AS phone_digits FROM customers;SELECT REPLACE(phone, '-', '') AS phone_digits FROM customers;