instr function

Applies to: Databricks SQL Databricks Runtime

Returns the (1-based) index of the first occurrence of substr in str.

Syntax

instr(str, substr)

Arguments

• str: A STRING expression.
• substr: A STRING expression.

Returns

A BIGINT.

If substr cannot be found the function returns 0.

Examples

SQL

> SELECT instr('SparkSQL', 'SQL');
 6

> SELECT instr('SparkSQL', 'R');
 0

> SELECT instr('SparkSQL' COLLATE UTF8_LCASE, 'sql');
 6

> SELECT instr('SparkSQL' COLLATE UTF8_BINARY, 'sql');
 6

Related functions

• locate function
• position function