Tutorial Gateway

MySQL LOCATE Function

by

The MySQL Locate function is one of the String Function, 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 in MySQL to find the index position with an example.

MySQL String Locate Syntax

The basic syntax of string Locate in MySQL is as shown below:

LOCATE(Substring, String)

LOCATE(Substring, String, Start_Position)

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

MySQL Locate Function Example 1

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

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

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

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

OUTPUT

MySQL LOCATE Function 1

In the example, we are using the Start Position of a MySQL String Locate function. The Second 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);

OUTPUT

MySQL LOCATE Function 2

MySQL Locate String Example 2

In this example, we implement this MySQL string locate function on different columns present in the employe table.

The following statement finds or locates 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;

OUTPUT

MySQL LOCATE Function 3