MySQL LOCATE() Function

LOCATE(substr, str) returns the 1-based position of the first occurrence of a substring. It returns 0 when the substring is absent and NULL if any argument is NULL.

SELECT LOCATE('xy', 'afghytyxyrt');

Output: 8

SELECT LOCATE('z', 'abcdefgh');

Output: 0

Start searching from a position

SELECT LOCATE('xy', 'axyghtxyrt', 4);

Search a table column

SELECT * FROM student WHERE LOCATE('john', name) > 0;

Matching rows:

The result depends on the column collation. For ordinary wildcard search, compare this with LIKE.

SELECT LOCATE('john', name) AS position, name
FROM student;

Sample positions:

Find @ in an email address

SELECT LOCATE('@', 'myname@example.com');

For a complete sample dataset, see the student table reference. You can combine LOCATE() with SUBSTRING() when a position is needed before extracting part of a string.

Download the student table SQL dump

For pattern-based alternatives, see REGEXP. For broader keyword matching, also see keyword search and SUBSTRING_INDEX().

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: