POSITION()
Returns the 1-based location of a substring within a string.
Description
POSITION finds where a substring first appears inside a string, returning the
1-based index (the first character is position 1). If the substring isn't found,
it returns 0. The SQL-standard syntax is POSITION(needle IN haystack).
Syntax
POSITION(substring IN string)Parameters
| Name | Description | Optional |
|---|---|---|
| substring | The text to locate. | No |
| string | The text to search. | No |
Return Type
Returns an INTEGER (0 if not found).
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
POSITION vs STRPOS
POSITION(sub IN str) and STRPOS(str, sub) do the same thing with reversed
argument order. Pick the one that reads better.
Common Mistakes
- 0-based expectation. SQL positions start at 1, not 0; not found is 0.
- Argument order. It's
POSITION(substring IN string), the reverse of many other functions. - Case sensitivity. Matching is case-sensitive; use
LOWERto normalize.
Related Functions
See also: SUBSTRING, STRPOS, LENGTH.