STRPOS

​

Syntax

​

Strpos form (scalar)

strpos(value, search[, start])

• Use STRING search and value operands and an INTEGER start of at least one.

​

Arguments

​

Returns

• Type: BIGINT; query tools may report INTEGER
• Possible values: One-based position or zero when absent.
• NULL behavior: Returns NULL if either string or a supplied start position is NULL. The start position defaults to 1.

​

Properties

​

Usage notes

• INSTR uses haystack, needle; LOCATE uses needle, haystack.
• STRPOS takes the text to search first, then the substring. CHARINDEX and LOCATE take the substring first, then the text to search.

​

Examples

​

Alias calling form: STRPOS

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

​

Find the first occurrence

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

​

Limitations

• The position is returned as BIGINT, even when query tools report INTEGER.
• Use a start position 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.