Oracle INSTR in Snowflake, Databricks and BigQuery
BigQuery INSTR takes the same four arguments as Oracle. Snowflake has no INSTR; use POSITION or CHARINDEX, and REGEXP_INSTR for the nth occurrence. Databricks instr only takes two arguments.
Oracle function: INSTR(string, substring [, position [, occurrence]]) · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | POSITION(substr, str [, start_pos]) | For the nth occurrence use REGEXP_INSTR(str, pattern, 1, n) with the substring escaped. | Snowflake docs |
| Databricks | Same name | instr(str, substr) | Two arguments only; locate(substr, str, pos) adds a start position. | Databricks docs |
| BigQuery | Same name | INSTR(value, subvalue [, position [, occurrence]]) | Same arguments as Oracle, including a negative position that searches from the end. | BigQuery docs |
Behavior differences to check
Snowflake
- POSITION and CHARINDEX take the substring first, the reverse of INSTR.
Databricks
- INSTR(s, x, -1) (last occurrence) has no direct form; use length(s) - instr(reverse(s), reverse(x)) - length(x) + 2, or a regular expression.
Examples
Position of the second comma
SELECT INSTR(csv_line, ',', 1, 2) AS second_comma FROM imports;SELECT REGEXP_INSTR(csv_line, ',', 1, 2) AS second_comma FROM imports;SELECT locate(',', csv_line, locate(',', csv_line) + 1) AS second_comma FROM imports;SELECT INSTR(csv_line, ',', 1, 2) AS second_comma FROM imports;