SQL Server CHARINDEX in Snowflake, Databricks and BigQuery
Snowflake and Databricks both accept CHARINDEX(substring, string [, start]) with the SQL Server argument order. BigQuery uses STRPOS(string, substring), with the arguments reversed, or INSTR for a start position.
SQL Server function: CHARINDEX(expressionToFind, expressionToSearch [, start_location]) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | CHARINDEX(expr1, expr2 [, start_pos]) | Same argument order; returns 0 when not found. POSITION is an alias. | Snowflake docs |
| Databricks | Same name | charindex(substr, str [, pos]) | Synonym of locate; 1-based, returns 0 when not found. | Databricks docs |
| BigQuery | Rewrite | STRPOS(value, subvalue) | Arguments reversed: the string to search comes first. Use INSTR(value, subvalue, position) for a start position. | BigQuery docs |
Behavior differences to check
- SQL Server comparisons follow the column collation, which is often case-insensitive. The targets compare case-sensitively by default, so CHARINDEX('abc', 'ABC') stops matching.
BigQuery
- Keeping the SQL Server argument order in STRPOS searches for the column inside the literal and almost always returns 0, without an error.
Examples
Domain part of an email address
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, 100) AS domain FROM users;SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, 100) AS domain FROM users;SELECT substring(email, charindex('@', email) + 1, 100) AS domain FROM users;SELECT SUBSTR(email, STRPOS(email, '@') + 1) AS domain FROM users;