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
-- Match at position 7
f1 := POSITION('World' IN 'Hello World'),
-- First `o` only
f2 := POSITION('o' IN 'Hello World'),
-- Match at the start
f3 := POSITION('Hello' IN 'Hello World')
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT |
|---|---|---|
| 7 | 5 | 1 |
Chained .POSITION(...)
FeatureQL
SELECT
-- Same search with the substring as the method argument
f1 := 'Hello World'.POSITION('World')
;Result
| f1 BIGINT |
|---|
| 7 |
Edge cases
FeatureQL
SELECT
-- Not found → 0
f1 := POSITION('xyz' IN 'Hello World'),
-- Empty needle → 1
f2 := POSITION('' IN 'abc'),
-- NULL haystack → NULL
f3 := POSITION('x' IN NULL(VARCHAR))
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT |
|---|---|---|
| 0 | 1 | NULL |