SQL Server PATINDEX in Snowflake, Databricks and BigQuery
PATINDEX takes a LIKE pattern with % and [] wildcards. The targets use REGEXP_INSTR with a regular expression, so the pattern itself must be rewritten.
SQL Server function: PATINDEX('%pattern%', expression) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | REGEXP_INSTR(subject, pattern) | Returns 0 when there is no match. | Snowflake docs |
| Databricks | Rewrite | regexp_instr(str, regexp) | Databricks SQL and Databricks Runtime 11.3 LTS and above. | Databricks docs |
| BigQuery | Rewrite | REGEXP_INSTR(source_value, regexp) | Returns 0 when there is no match. | BigQuery docs |
Behavior differences to check
- Translate the LIKE pattern: drop the outer %, turn _ into ., keep [0-9] classes. PATINDEX('%[0-9]%', s) becomes REGEXP_INSTR(s, '[0-9]').
- SQL Server matching follows the column collation, often case-insensitive; regular expressions in the targets are case-sensitive unless a flag or (?i) is used.
Examples
Position of the first digit
SELECT PATINDEX('%[0-9]%', code) AS first_digit FROM products;SELECT REGEXP_INSTR(code, '[0-9]') AS first_digit FROM products;SELECT regexp_instr(code, '[0-9]') AS first_digit FROM products;SELECT REGEXP_INSTR(code, r'[0-9]') AS first_digit FROM products;