MySQL LOCATE Function

The MySQL Locate function is one of the String methods, which is useful to find the first occurrence of a substring in a string expression and returns the index value. Let me show you how to write String Locate to find the index position with an example, and the basic syntax of it is as shown below:

LOCATE(Substring, String)

LOCATE(Substring, String, Start_Position)

This MySQL Locate function returns the index position of the first occurrence of a substring. If we provide the Start_Position, then this method starts looking from that index position and returns the first occurrence after that position.

MySQL Locate Function Example

The String Locate Function returns the index position. The following query shows multiple ways to use this function.

SELECT LOCATE('l', 'Hello');

SELECT LOCATE('o', 'Helloworld');

SELECT LOCATE('j', 'Helloworld'); 

In the example, we are using the Start Position of a String Locate function. The Second MySQL statement starts looking from the 10th position, and the third statement starts looking from the 26th position.

SELECT LOCATE('abc', 'This is abc working in abc Company');

SELECT LOCATE('abc', 'This is abc working in abc Company', 10);

SELECT LOCATE('abc', 'This is abc working in abc Company', 26);

In this example, we implement this on different columns present in the employe table. The following String method statement finds the first occurrence of @, com, and mail words from the Email column.

SELECT FirstName,
       LastName,
       DepartmentName,
       Email,
       LOCATE('@', Email),
       LOCATE('com', Email),
       LOCATE('mail', Email)
 FROM `MySQL Tutorial`.employe;