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. 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 substringbased on the position of another string. The examples used extract data from an XML string stored in the database.

Related posts:

• Selecting substrings with MySQL using LOCATE and SUBSTRING
• String concatenation with MySQL
• Using SELECT REPLACE with MySQL
• Upper case and lower case strings with MySQL
• Find the length of the longest string in MySQL