POSITION()

All functions > STRING > POSITION()

Returns the position of the first occurrence of a substring within a string.

Signatures

Returns: Position of first occurrence (1-indexed), or 0 if not found

POSITION(substring: VARCHAR, string: VARCHAR) → BIGINT

Notes

• Position is 1-indexed (first character is position 1)
• Returns 0 if substring is not found
• An empty substring is treated as found at position 1
• Case-sensitive search
• If either argument is NULL the result is NULL (use NULL(VARCHAR); bare NULL fails inference)

Related operators

• POSITION

Examples

POSITION(...)

SELECT
    f1 := POSITION('World' IN 'Hello World'), -- `POSITION(search IN string)` form
    f2 := POSITION('o' IN 'Hello World') -- First occurrence only
;

.POSITION(...) — chained

SELECT
    f1 := 'Hello World'.POSITION('World') -- Chained on the haystack
;

Edge cases

SELECT
    f1 := POSITION('xyz' IN 'Hello World'), -- Not found returns 0
    f2 := POSITION('ABC' IN 'ABCABC'), -- Found at beginning
    f3 := POSITION('' IN 'abc'), -- Empty substring at start
    f4 := POSITION('x' IN NULL(VARCHAR)) -- NULL yields NULL
;