Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.

LOCATE

Returns the first position of the given search string.

Syntax

LOCATE (needle, haystack)
LOCATE (needle, haystack, startpos)

Arguments

  • needle: string to search for
  • haystack: any string or binary object
  • startpos: character position to start searching at
Info
This function is identical to INSTR but with the arguments reversed.

Return Type

Integer position, one-indexed. 0 if not found.

Examples

+---------------------+
| LOCATE('i', 'ohai') |
+---------------------+
|                   4 |
+---------------------+

SELECT LOCATE('z', 'ohai');
+---------------------+
| LOCATE('z', 'ohai') |
+---------------------+
|                   0 |
+---------------------+

SELECT LOCATE('i', 'ohaiohai', 6);
+----------------------------+
| LOCATE('i', 'ohaiohai', 6) |
+----------------------------+
|                          8 |
+----------------------------+