How to find the first occurrence of a substring in a string in MySQL?

MySQL LOCATE() function can find the first occurrence of a substring in a string. The following is the syntax to use it -

Syntax

LOCATE(Substring, String)

In this function, Substring is the string from which the occurrence of the substring needs to be found, and string is the string from which the occurrence of the substring needs to be searched .

We must pass both the strings (i.e. the substring to search for and the string to search for the substring from) as arguments to 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 the substring "good" is the 10th from the string "RAM IS A GOOD BOY" position to start.

However, we can also manage the starting point of the search with the help of another parameter representing the location. The syntax of LOCATE() with positional parameters is 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 in using positional parameters. If we don't use it then by default the search starts from position 1 and when we specify "5" as position parameter in the example it will start from position 5.

The above is the detailed content of How to find the first occurrence of a substring in a string in MySQL?. For more information, please follow other related articles on the PHP Chinese website!