MySQL LOCATE() Function

Summary: in this tutorial, you will learn how to use the MySQL LOCATE() function to find the position of a substring within a given string.

Introduction to MySQL LOCATE() function

The LOCATE() function returns the position of a substring within a given string. The LOCATE() function has the following syntax:

LOCATE(substring, string, position)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• substring: The substring that you want to locate within the main string.
• string: The main string that you want to find the substring.
• position: The position within the main string where you want to start searching for the substring. The position is optional. If you omit it, the search starts from the beginning of the string.

If the LOCATE() function cannot find the substring in the string starting at the position, it returns 0.

If any argument is NULL, the LOCATE() function returns NULL.

MySQL LOCATE() function examples

Let’s take some examples of using the LOCATE() function.

1) Simple MySQL LOCATE() function example

The following example uses the LOCATE() function to find the position of the substring 'MySQL' in the string 'Hello, MySQL':

SELECT 
  LOCATE('MySQL', 'Hello, MySQL') position;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| position |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the LOCATE() function returns 8 indicating the string 'MySQL' starts at the 8th position in the string 'Hello, MySQL'.

Since we don’t use the position parameter, the LOCATE() function starts searching for the substring from the beginning of the string.

2) Using LOCATE() function using the position parameter

The following example uses the LOCATE function to find the substring "know" in the string “You don't know what you don't know" starting from position 12:

SELECT 
  LOCATE(
    'know', "You don't know what you don't know", 
    12
  ) position;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| position |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The main string "You don't know what you don't know" has two words "know". The first substring "know" starts at position 11 and the second one starts at position 31 in the main string.

Because we use the value 12 for the position argument, the LOCATE() function starts searching for the substring "know" at position 12 and finds it at position 31.

3) Handling cases with no match

If the substring is not found in the main string, the LOCATE() function returns 0. For example:

SELECT 
  LOCATE('alien', 'earth');Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------+
| LOCATE('alien', 'earth') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The LOCATE() function returns 0 because it cannot find the string "alien" in the string "earth".

4) Using LOCATE() function with the table data

We’ll use the products table from the sample database:

The following example uses the LOCATE() function to find the product that has the word "Harley Davidson" in the product description:

SELECT 
  productName 
FROM 
  products 
WHERE 
  LOCATE(
    "Harley Davidson", productDescription
  ) > 0;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------------+
| productName                          |
+--------------------------------------+
| 2003 Harley-Davidson Eagle Drag Bike |
+--------------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL LOCATE() function to find the position of a substring within a given string starting at a specified position.