How is it possible in MySQL to find the location of the first occurrence of a substring in a string?

MySQL LOCATE() function makes it possible to find the location of the first occurrence of a substring in a string. Following is the syntax of using it −

Syntax

LOCATE(Substring, String)

In this function, Substring is the string whose position of occurrence needs to find and the string is a string from which the occurrence of substring needs to be searched.

We must have to pass both the strings (i.e. substring, which is to be searched and the string, from which substring is to be searched) as arguments of the LOCATE() function.

Example

mysql> Select LOCATE('Good','RAM IS A GOOD BOY')As Result;
+--------+
| Result |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

In the above example, the output is 10 because the first occurrence of substring ’good’ start from 10th position in the string ‘RAM IS A GOOD BOY’.

However, we can also manage the starting point of searching with the help of another argument representing position. The syntax of LOCATE() with position argument would be as follows −

LOCATE(Substring, String, position)

Example

mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY')As Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY',5)As Result;
+--------+
| Result |
+--------+
|     16 |
+--------+
1 row in set (0.00 sec)

From the above result set, we can see the difference between using position argument. If we do not use it the search by default starts from the 1st position and when we specify ‘5’ as the position argument in our example it starts from 5th position.