LOCATE
On this page
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
Note
This function is identical to INSTR but with the arguments reversed.
Return Type
Integer position, one-indexed.
Examples
SELECT LOCATE('i', 'ohai');
+---------------------+
| LOCATE('i', 'ohai') |
+---------------------+
| 4 |
+---------------------+SELECT LOCATE('z', 'ohai');
+---------------------+
| LOCATE('z', 'ohai') |
+---------------------+
| 0 |
+---------------------+SELECT LOCATE('i', 'ohaiohai', 6);
+----------------------------+
| LOCATE('i', 'ohaiohai', 6) |
+----------------------------+
| 8 |
+----------------------------+Caution
Implicit Collation
When character_ is set to utf8, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters.utf8mb4 character set.
For more information, refer to Implicit Collation in Special Cases.
Last modified: April 4, 2023