INSTR

​

Syntax

​

Scalar

instr(value, search [, start [, occurrence]])

​

Arguments

​

Returns

• Type: BIGINT; query tools may report INTEGER
• Possible values: One-based position or zero when absent.
• NULL behavior: NULL string/search, or a supplied NULL start/occurrence operand, returns NULL. Omitted start and occurrence default to one.

​

Properties

​

Usage notes

• INSTR uses haystack, needle; LOCATE uses needle, haystack.

​

Examples

​

Typical behavior

SELECT instr('Hello', 'll') AS "value";

​

Find the first occurrence

SELECT instr('banana', 'an') AS first_match, instr('banana', 'xy') AS missing;

​

Limitations

• The position is returned as BIGINT, even when query tools report INTEGER.
• Use start and occurrence values of at least 1. Zero or negative values can fail or give unexpected results. For non-ASCII text, a start position beyond the string’s length can restart the search at the beginning.