STRPOS()

All functions > STRING > STRPOS()

Returns the position of a substring within a string, optionally finding the N-th occurrence.

Signatures

Returns: Position of the substring (1-indexed), or 0 if not found

STRPOS(string: VARCHAR, substring: VARCHAR, [occurrence: BIGINT]) → BIGINT

Notes

• Position is 1-indexed
• Returns 0 if substring not found
• Case-sensitive search
• Optional occurrence (N-th match) is only accepted when supported; otherwise use the two-argument form (first match only)
• If any argument is NULL the result is NULL (use NULL(VARCHAR) / NULL(BIGINT); bare NULL fails inference)
• See also: POSITION

Examples

SELECT
    f1 := STRPOS('Hello World', 'World'), -- Basic search
    f2 := STRPOS('Hello Hello World', 'Hello'), -- First occurrence
    f3 := STRPOS('test', 'xyz'), -- Not found
    f4 := STRPOS(NULL(VARCHAR), 'a') -- NULL yields NULL
;