LOCATE

​

Syntax

​

Scalar

locate(search, value[, start])

​

Strpos form (scalar)

strpos(value, search[, start])

• Use STRING search and value operands and an INTEGER start of at least one.

​

Charindex form (scalar)

charindex(search, value[, start])

• Use STRING search and value operands and an INTEGER start of at least one.

​

Arguments

​

Scalar, charindex form

​

Strpos form

​

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.
• STRPOS takes the text to search first, then the substring. CHARINDEX and LOCATE take the substring first, then the text to search.

​

Examples

​

Typical behavior

SELECT locate('ll', 'Hello') AS "value";

​

Alias calling form: STRPOS

SELECT strpos('Hello', 'll') AS "value";

​

Alias calling form: CHARINDEX

SELECT charindex('ll', 'Hello') AS "value";

​

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.