MySQL LOCATE() – Find Substrings in a String with MySQL

In this tutorial, we will study the MySQL LOCATE() function. The LOCATE() function is a string function which is used to find out the position of the first occurrence of a substring within a string. If the string does not contain the substring, then the position is returned as 0. The LOCATE() function performs a multi-byte safe and case-insensitive search. LOCATE() and POSITION() have one significant difference – LOCATE() allows us to set a starting point for the operation. This means we can set a starting position for the string from where the search for the substring should take place. The LOCATE() function is usually used with the SELECT statement.

Syntax of MySQL LOCATE()

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

Where ‘substring’ is the string that is to be searched within another given argument ‘string’. The argument ‘start’ is an optional argument that is used to specify the starting position for the search. If it is not mentioned, then LOCATE() assumes it to be 1.

Examples of MySQL LOCATE()

Let’s look at some of the examples of the MySQL LOCATE function.

In the below queries, we have made the use of an alias named Result so that the output is readable.

Consider the below query.

SELECT LOCATE('Dev', 'Dev in JournalDev', 4) AS Result;Code language: SQL (Structured Query Language) (sql)

What the above query does is, it sets the starting point for the operation as position 4 (the whitespace after the first ‘Dev’) and checks for the substring ‘Dev’ from that position. We get our output as,

MySQL LOCATE() is case insensitive

Let us see how LOCATE() is case insensitive. Take a look at the below query.

SELECT LOCATE('Dog', "I have a dog and she has a cat.", 5) AS Result;Code language: SQL (Structured Query Language) (sql)

And our output is,

After position 5, our string is “ve a dog and she has a cat.”. As evident, LOCATE() ignores the case of the alphabets and returns the position of ‘Dog’ in the given string.

Behaviour with no matches

MySQL LOCATE() returns 0 if the substring is not found in a given string. Let us look at the below example.

SELECT LOCATE('Dog', "I have a dog and she has a cat.", 12) AS Result;Code language: SQL (Structured Query Language) (sql)

After position 12, our string is “g and she has a cat.” and since there is no occurrence of ‘Dog’ in this string, we get our output as,

NULL values with LOCATE()

LOCATE() returns NULL if any of its arguments is a NULL value. Let’s demonstrate that using the below query,

SELECT LOCATE(NULL, 'SQL is great!', 2) AS Result;Code language: SQL (Structured Query Language) (sql)

And we get our output as NULL as shown below –

Using MySQL LOCATE() with Table data

Consider the below Employee table.

Example 1

Now, consider the below query –

SELECT Name, LOCATE('N', Name, 4) AS Result FROM Employee;Code language: SQL (Structured Query Language) (sql)

With this query, we are looking for those values in the ‘Name’ column that have the letter ‘N’ on or after the 4th character. The output is,

Example 2

Now, what if we don’t want to see the entries which do not have the letter ‘N’ on or after the 4th character in them? LOCATE() can be used in the WHERE clause. The query for this is,

SELECT eid, Name, LOCATE('N', Name, 4) AS Result FROM Employee WHERE LOCATE('N', Name, 4)>0;Code language: SQL (Structured Query Language) (sql)

And we get our output as only those names that have the letter ‘N’ on or after the 4th character in them.

Difference between LOCATE() and POSITION()

LOCATE() and POSITION() essentially do the same thing. Both return the position of the first occurrence of a substring within a string. But there is a significant difference in how we can work with them. In short, POSITION() lacks a feature when compared to LOCATE().

You see, LOCATE() has an optional argument (third argument) called ‘start’ as mentioned in the syntax earlier. This argument is used to specify what position onwards we want LOCATE() to start searching for the substring. POSITION() does not have such an argument.

Suppose you have a variable @str as follows:

SET @str = 'Angola, Argentina, Australia, Austria, Afghanistan, Australia, Armenia';Code language: SQL (Structured Query Language) (sql)

Now, what if you want to find the first occurrence of ‘Australia’ in the variable? We will use the following query,

SELECT POSITION('Australia' IN @str) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

That is all good. But what if we want to check if there is any other occurrence of ‘Australia’ in the variable @str? Since POSITION() will always return the first occurrence and have no provision to specify a starting point from where the search should occur, we will use LOCATE(). Let us add one to the previous result and put that in as our ‘start’ argument.

SELECT LOCATE('Australia', @str, 21) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

Conclusion

The MySQL LOCATE() function is a useful function to check if a string is present in a string. It can be very useful in exploratory data analysis and string functions.