Tutorial Gateway

MySQL LOCATE Function

by

The MySQL Locate function is one of the String Function, which is used to find the first occurrence of a substring in a string expression, and returns the index value.

In this article we will 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 function returns the index position of a first occurrence of a substring. If we provided the Start_Position then this function will start looking from that index position, and returns the first occurrence after that position.

MySQL Locate Function Example 1

The String Locate Function is used to return the index position. The following query will show 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 will start looking from 10th position, and the third statement starts looking from 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 will find or locate the first occurrence of @, com and mail words from Email column.

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

OUTPUT

MySQL LOCATE Function 3

Thank You for Visiting Our Blog