Finding the location of a string in a string with MySQL

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.

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.