POSITION
All functions > STRING > POSITION
Returns the 1-based index of the first occurrence of a substring within a string.
Syntax
POSITION(search IN string) · string.POSITION(search)
Notes
- Returns 0 when the substring does not occur; 1 when an empty substring is searched
- Search is case-sensitive
- If either value is NULL the result is NULL (use
NULL(VARCHAR); bareNULLfails inference) - For the same search spelled as a two-argument function, see Related functions (
STRPOS)
Related Functions
Examples
POSITION ... IN ...
FeatureQL
SELECT
f1 := POSITION('World' IN 'Hello World'), -- Match at position 7
f2 := POSITION('o' IN 'Hello World'), -- First `o` only
f3 := POSITION('Hello' IN 'Hello World') -- Match at the start
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT |
|---|---|---|
| 7 | 5 | 1 |
Chained .POSITION(...)
FeatureQL
SELECT
f1 := 'Hello World'.POSITION('World') -- Same search with the substring as the method argument
;Result
| f1 BIGINT |
|---|
| 7 |
Edge cases
FeatureQL
SELECT
f1 := POSITION('xyz' IN 'Hello World'), -- Not found → 0
f2 := POSITION('' IN 'abc'), -- Empty needle → 1
f3 := POSITION('x' IN NULL(VARCHAR)) -- NULL haystack → NULL
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT |
|---|---|---|
| 0 | 1 | NULL |