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 finds the N-th match; same semantics on every SQL backend
• If any argument is NULL the result is NULL (use NULL(VARCHAR) / NULL(BIGINT); bare NULL fails inference)
• See also: POSITION

Examples

SELECT
    -- Basic search
    f1 := STRPOS('Hello World', 'World'),
    -- First occurrence
    f2 := STRPOS('Hello Hello World', 'Hello'),
    -- Explicit first occurrence
    f3 := STRPOS('Hello Hello World', 'Hello', 1),
    -- Second occurrence
    f4 := STRPOS('Hello Hello World', 'Hello', 2),
    -- Third occurrence
    f5 := STRPOS('ABCABCABC', 'ABC', 3),
    -- Not found
    f6 := STRPOS('test', 'xyz'),
    -- NULL yields NULL
    f7 := STRPOS(NULL(VARCHAR), 'a')
;