POSITION

​

Syntax

​

Scalar

position(search IN value [FROM start])

​

Arguments

​

Returns

• Type: BIGINT; query tools may report INTEGER
• Possible values: One-based position or zero when absent.
• NULL behavior: NULL string/search, or a supplied NULL start/occurrence operand, returns NULL. Omitted start and occurrence default to one.

​

Properties

​

Usage notes

• INSTR uses haystack, needle; LOCATE uses needle, haystack.
• In the standard POSITION syntax, put the substring to find before the text to search. Use the two- or three-argument forms shown here.

​

Examples

​

Example values

SELECT position('ll' IN 'Hello') AS "value";

​

Find the first occurrence

SELECT position('an' IN 'banana') AS first_match, position('xy' IN 'banana') AS missing;

​

Limitations

• The position is returned as BIGINT, even when query tools report INTEGER.
• Use start and occurrence values of at least 1. Zero or negative values can fail or give unexpected results. For non-ASCII text, a start position beyond the string’s length can restart the search at the beginning.