SingleStore DB

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

Notice

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 |
+----------------------------+