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) → BIGINTsql
| Parameter | Type | Required | Description |
|---|---|---|---|
substring | VARCHAR | Yes | Substring to search for |
string | VARCHAR | Yes | String to search in |
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); bareNULLfails inference)
Related operators
Examples
POSITION(...)
FeatureQL
SELECT
-- `POSITION(search IN string)` form
f1 := POSITION('World' IN 'Hello World'),
-- First occurrence only
f2 := POSITION('o' IN 'Hello World')
;Result
| f1 BIGINT | f2 BIGINT |
|---|---|
| 7 | 5 |
.POSITION(...) — chained
FeatureQL
SELECT
-- Chained on the haystack
f1 := 'Hello World'.POSITION('World')
;Result
| f1 BIGINT |
|---|
| 7 |
Edge cases
FeatureQL
SELECT
-- Not found returns 0
f1 := POSITION('xyz' IN 'Hello World'),
-- Found at beginning
f2 := POSITION('ABC' IN 'ABCABC'),
-- Empty substring at start
f3 := POSITION('' IN 'abc'),
-- NULL yields NULL
f4 := POSITION('x' IN NULL(VARCHAR))
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT | f4 BIGINT |
|---|---|---|---|
| 0 | 1 | 1 | NULL |