How to Use the MySQL LOCATE Function
This tutorial explains how to use the MySQL LOCATE function, which returns the position where the substring first appears in a given string.
See also:
MySQL LOCATE() Syntax
The syntax of the LOCATE function is as follows:
LOCATE(substring, string)
or:
LOCATE(substring, string, position)
Remarks
- LOCATE() function returns 0 if the substring or the string is NULL or returns 0 if the substring is not in the string.
- The first syntax returns the position of the first occurrence of the substring in the string, starting from the first position.
- The second syntax returns the position of the first occurrence of the substring in the string, starting from the specified position.
LOCATE() Examples
Example 1
The following statement returns the position of the first occurrence of "tutorial":
SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use LEFT JOIN, and the second tutorial teaches you how to use RIGHT JOIN.") AS Position;
Result: 35
There are three "tutorial" words in the specified string, the first occurrence of which is the 35th position.
Example 2
The following statement returns the position of the first occurrence of "guide":
SELECT LOCATE("guide","Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use LEFT JOIN, and the second tutorial teaches you how to use RIGHT JOIN.") AS Position;
Result:
0
Since there is no word guide in the string specified above, LOCATE() function returns 0.
Example 3
The following statement returns the position of the first occurrence of "tutorial," starting at position 30:
SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use INNER JOIN, and the second tutorial teaches you how to use FULL JOIN.", 30) AS Position;
Result:
35
Example 4
The following statement returns the first occurrence of "tutorial," starting at the 36th position:
SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use INNER JOIN, and the second tutorial teaches you how to use FULL JOIN.", 36) AS Position;
Result:
56
Summary
In this tutorial, you have learned how to use the MySQL LOCATE function. The LOCATE function is a function that returns the position of the first occurrence of a substring in a given string.
You can use the LOCATE function to get the position of the first occurrence of a substring from the first or the specified position in a given string.