There may be times when you need to find the location of a string within a string with MySQL. The LOCATE() function allows you to do this and I show some examples in this post of how to do it. The next MySQL post on this blog (this time next week) will combine LOCATE() with SUBSTRING() to extract substrings from a string.
Using Locate
LOCATE works like this:
LOCATE(<string to find>, <string to find it in>)
or
LOCATE(<string to find>, <string to find it in>, <position to start at>)
For example, to find the location of "oranges" in the string "apples oranges pears bananas apples oranges" do this:
SELECT LOCATE("oranges", "apples oranges pears bananas apples oranges");
This will return 8. To find the next occurance of "oranges" in the source string, pass the third parameter to the function telling it to start from position 9 in this example:
SELECT LOCATE("oranges", "apples oranges pears bananas apples oranges", 9);
This will now return 37. Note that in these examples if 8 had been passed as the starting position then 8 would have been returned.
Combining with SUBSTRING
In next week’s MySQL post I’ll look at how to combine the LOCATE function with SUBSTRING to extract a substring based on the position of another string. The examples used extract data from an XML string stored in the database.