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.
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
Syntax of MySQL LOCATE()
LOCATE(substring, string, start)
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;
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;
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
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;
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;
And we get our output as NULL as shown below –
Using MySQL LOCATE() with Table data
Consider the below Employee table.
Now, consider the below query –
SELECT Name, LOCATE('N', Name, 4) AS Result FROM Employee;
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,
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;
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()
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() 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';
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;
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;
And the output is,
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.