Searching for Substrings
Problem
You want to know whether a given string occurs within another string.
Solution
Use LOCATE()
.
Discussion
The LOCATE()
function
takes two arguments representing the substring that you’re looking for
and the string in which to look for it. The return value is the
position at which the substring occurs, or 0
if it’s not present. An optional third
argument may be given to indicate the position within the string at
which to start looking.
mysql>SELECT name, LOCATE('in',name), LOCATE('in',name,3) FROM metal;
+----------+-------------------+---------------------+
| name | LOCATE('in',name) | LOCATE('in',name,3) |
+----------+-------------------+---------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 5 | 5 |
| silver | 0 | 0 |
| tin | 2 | 0 |
+----------+-------------------+---------------------+
LOCATE()
uses the
collation of its arguments to determine whether the search is
case-sensitive. See Recipes and for information about changing the
comparison properties of the arguments if you want to change the
search behavior.
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.