How the LOCATE() Function Works in MySQL

In MySQL, the LOCATE() function returns the position of a substring within a string. More specifically, it returns the position of the first occurrence within the string, or the first occurrence after a given starting point.

Syntax

It can be used in either of the following ways:

LOCATE(substr,str)
LOCATE(substr,str,pos)

Where substr is the substring to locate, and str is the string to search.

When using the second syntax, pos is the position to start searching.

Example 1 – First Syntax

Here’s an example using the first syntax:

SELECT LOCATE('cat', 'One cat jumped over the other cat') AS Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

Example 2 – Second Syntax

Here’s an example where we specify a start position to start searching:

SELECT LOCATE('cat', 'One cat jumped over the other cat', 6) AS Result;

Result:

+--------+
| Result |
+--------+
|     31 |
+--------+

In this case, the first occurrence of cat begins at position 5, but I specified the search to start at position 6. Therefore, the position of the next occurrence of that string was the one that was returned.

Note that, although the search started at position 6, the function still returns the position of the substring within the string – not from the start position.

Here’s another example to help make this clearer.

SELECT 
  LOCATE('c', 'a b c', 1) AS 'Result 1',
  LOCATE('c', 'a b c', 2) AS 'Result 2',
  LOCATE('c', 'a b c', 4) AS 'Result 3';

Result:

+----------+----------+----------+
| Result 1 | Result 2 | Result 3 |
+----------+----------+----------+
|        5 |        5 |        5 |
+----------+----------+----------+

The result is the same no matter where we start searching.

Example 3 – Locating Part of a Word

The substring can be part of a longer word:

SELECT LOCATE('sing', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

In fact, there’s no requirement for it to even be a word (after all, we’re simply searching a string):

SELECT 
  LOCATE('z23!#', 'u_4, z23!#') AS 'Result 1',
  LOCATE(' ', 'a b c') AS 'Result 2',
  LOCATE(',', 'cat, dog, bird') AS 'Result 3';

Result:

+----------+----------+----------+
| Result 1 | Result 2 | Result 3 |
+----------+----------+----------+
|        6 |        2 |        4 |
+----------+----------+----------+

Example 4 – No Matches

If the substring isn’t found, 0 is returned:

SELECT LOCATE('Bat', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 5 – Case Sensitivity

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

Therefore the following works on nonbinary strings, even though the case doesn’t match:

SELECT LOCATE('Sing', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

But if we use a binary string, this happens:

SET @str = BINARY 'Increasingly'; 
SELECT LOCATE('Sing', @str) AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

But of course, if we change it so that the case matches, we get a match:

SET @str = BINARY 'Increasingly'; 
SELECT LOCATE('sing', @str) AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

Example 6 – NULL Arguments

If any of the arguments are NULL, NULL is returned:

SELECT 
  LOCATE(NULL, 'Increasingly') a,
  LOCATE('Bat', NULL) b,
  LOCATE('Bat', 'Increasingly', NULL) c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Selecting substrings with MySQL using LOCATE and SUBSTRING

The MySQL function SUBSTRING allows you to extract a substring from a string. This post looks at how to use substring to extract some information from a field in a database using an example column which contains XML data. Refer also to my earlier post titled "Finding the location of a string in a string with MySQL" for more information about using the LOCATION function.

Example Report

In one of the websites I manage, all of the enquiries and orders are saved into an XML format and then sent through to the remote system where they are then saved into that database. For auditing purposes all enquiries and their associated XML are logged into the web server database should we need to run some report or other on it at a later time (or resend an order).

In the example below the table is called enquiry_log and contains, among other fields, a field called "xml" which contains the xml string. There's an XML node called <Referrer_To_Website> which contains a code logging which partner website referred the customer to this website.

Once a month we run a report to get the partner enquiries from the database. It would be easier to have the partner code as a separate column in the table but when the system was created we didn't need to run a report like this so for the moment it's easier to extract it from the XML.

SUBSTRING Arguments

SUBSTRING takes two or three arguments. The first two are the string or column name and the starting position to take the substring from. The third argument is the length of the string to extract. If the third argument is left out then the rest of the string from length is returned.

Example SQL

So, in order to get the text between <Referrer_To_Website> and </Referrer_To_Website> we need to do this:

SELECT
    SUBSTRING(log.xml, 
      LOCATE('<Referrer_To_Website>', log.xml)+21, 
      LOCATE('</Referrer_To_Website>', log.xml) - LOCATE('<Referrer_To_Website>', log.xml) - 21) 
    AS Referrer_To_Website
FROM 
    enquiry_log AS log

In the above SQL query, the second parameter passed to SUBSTRING() is the column from the enquiry_log table that contains the xml.

The second parameter is
LOCATE('<Referrer_To_Website>', log.xml)+21.

This returns the position of the first occurence of <Referrer_To_Website> in the "xml" column. 21 is then added to it; this is the length of the text <Referrer_To_Website> and the length needs to be added so SUBSTRING starts getting the substring from the first character after the end of the XML node name.

The third parameter is for the length of the string to extract. In plain speak, the formula to do this is (location of opening node) minus (location of the closing node) minus (the length of the opening node).

The resulting calculation for this example looks like:
LOCATE('</Referrer_To_Website>', log.xml)
- LOCATE('<Referrer_To_Website>', log.xml)
- 21

I hope this all makes sense. It's a fairly specific type of example but does illustrate how you can extract strings from a MySQL column using LOCATE and SUBSTRING.

Related posts:

• MySQL: Using IF in a WHERE clause
• Finding the location of a string in a string with MySQL
• 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

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