intro
Let’s go over everything you need to know about the MySQL LOCATE function, which finds the position of a substring within a given string.
Dealing with substrings is one of the most common operations in MySQL string management. This generally includes extracting parts of strings from given inputs, as well as finding the position of specific substrings within a string (e.g., for data validation, data cleanup, and more). This is where the MySQL LOCATE function comes in!
By the end of this guide, you will know what the LOCATE function is in MySQL, how it works, how to use both of its syntaxes, and when it is useful in practical scenarios.
Let’s dive in!
What Is the LOCATE function in MySQL?
The MySQL LOCATE function is a string function that returns the starting position of the first occurrence of a substring within a given string. Some possible use scenarios where this function comes in handy are:
As you can see, LOCATE is useful in many search-related use cases. As the name suggests, it allows you to locate specific substrings (which can be seen as a keyword or key phrase in a search scenario) within a given string input or database field.
MySQL LOCATE: Syntax and Usage
The LOCATE function in MySQL supports two possible syntaxes. Let's start with the first one:
1
LOCATE(substr, str)
Here, substr and str are both strings. The function returns the position (as a 1-based index) of the first occurrence of substr in str.
So, for example, consider this query:
1
SELECT LOCATE("Jar", "Jar Jar Binks");
The result would be:
1
1
The first occurrence of "Jar" in "Jar Jar Binks" starts at the very first character, which is numbered as 1 (and not 0, as the result is 1-based).
The second supported syntax is:
1
LOCATE(substr, str, pos)
This time, the function starts searching from the 1-based position pos.
So, take a look at this example:
1
SELECT LOCATE("Jar", "Jar Jar Binks", 2);
This time, the result will be:
1
5
This happens because the search begins at the second character, so "Jar Jar Binks" is treated as if it were something like "_ar Jar Binks". The next "Jar" starts at position 5 in the original string.
In both syntaxes, LOCATE returns 0 if *substr* is not in *str*:
1
SELECT LOCATE("Obi", "Jar Jar Binks"); -- Returns 0
2
SELECT LOCATE("Obi", "Jar Jar Binks", 3); -- Returns 0
Additional notes:
MySQL LOCATE Examples
Explore some MySQL LOCATE examples to better understand how the function works and where you can use it.
Note: The examples below can be executed in DbVisualizer, a feature-rich, top-rated, multi-database client that fully supports MySQL, along with many other databases.
The following queries will be launched against the messages table below:
As you can see, it records messages sent internally in a company messaging system.
Example #1: Email Domain Extraction
Suppose you want to retrieve the email domain for each sender. You can achieve this by combining LOCATE with SUBSTRING, which is a pretty common use case:
1
SELECT sender, SUBSTRING(sender, LOCATE("@", sender) + 1) AS domain
2
FROM messages;
The MySQL SUBSTRING function extracts a portion of a string starting from a specified position. In this case, it starts right after the @ character (the + 1 is required to remove the "@" character from the resulting domains) and continues to the end of the email address, effectively extracting the domain.
Verify that by executing the query in DbVisualizer:
Notice how the email domains are correctly extracted as expected.
Example #2: Content Classification
Suppose you want to classify messages based on whether they contain specific keywords. For example, this is useful for analyzing logs or automated test messages.
You can use LOCATE to check if alerts contain a certain word and assign a priority conditionally:
1
SELECT id, subject, body,
2
CASE WHEN LOCATE("urgent", body) != 0 THEN "Top priority"
3
WHEN LOCATE("error", body) > 1 THEN "High priority"
4
ELSE "Regular priority"
5
END AS priority
6
FROM messages
7
WHERE subject = "Alert";
If you are not familiar with this syntax, take a look at our guide on the SQL CASE expression.
Run the above query in DbVisualizer:
Now the messages are prioritized based on the presence of keywords, as intended.
Example #3: Search by Keywords
Consider a scenario where you want to find all messages containing a specific word. For instance, you might want to locate messages that include "password" for security review purposes. You can use the MySQL LOCATE function in the WHERE clause to filter these messages:
1
SELECT id, body
2
FROM messages
3
WHERE LOCATE("password", body) > 0;
This query returns all messages that contain the word "password" , along with the position of the word in the text:
Et voilà! You just mastered the LOCATE function in MySQL.
Conclusion
In this blog post, you learned about the LOCATE function in SQL. Specifically, you understood that it finds the position of a substring within a given string. You also saw how to apply it in real-world use cases and scenarios.
As shown here, DbVisualizer makes it easier to write, execute, and debug SQL queries. Other than MySQL, supports over 50 databases and equips you with powerful features like SQL formatting, smart auto-complete, ER diagram generation, query optimization tools, and more. Download DbVisualizer for free today!
FAQ
What is the difference between the MySQL LOCATE and SUBSTRING functions?
In MySQL, the LOCATE function finds the position of the first occurrence of a substring within a string. In other words, it returns its index—or 0, if not found. On the contrary, SUBSTRING (or the equivalent SUBSTR alias) extracts a portion of a string starting from a specified position and for a given length. Simply put, LOCATE tells you where a substring is, while SUBSTRING gives you a substring. So, the two functions are different but complementary.
What are other useful MySQL string functions?
Other useful MySQL string functions are:
| Name | Description |
|---|---|
CONCAT() | Combines multiple strings into a single string. |
CONCAT_WS() | Concatenates strings using a specified separator. |
SUBSTRING() | Returns a portion of a string starting from a given position. |
LOCATE() | Returns the position of the first occurrence of a substring. |
LENGTH() | Returns the length of a string in bytes. |
CHAR_LENGTH() | Returns the number of characters in a string. |
REPLACE() | Replaces all occurrences of a substring with another string. |
TRIM() | Removes leading and trailing spaces from a string. |
UPPER() | Converts all characters in a string to uppercase. |
LOWER() | Converts all characters in a string to lowercase. |
Is the MySQL LOCATE function case-sensitive?
The LOCATE() function in MySQL is multibyte-safe, which means it is case-sensitive only if at least one of the arguments is a binary string. For example:
1
SELECT LOCATE("a", BINARY "Aka");
This returns:
1
3
You get the same result if you use the utf8mb4_bin collation explicitly (binary collation):
1
SELECT LOCATE("a", "Aka" COLLATE utf8mb4_bin);
Without the binary conversion or binary collation, LOCATE() follows the column or string collation rules, which are usually case-insensitive for standard text:
1
SELECT LOCATE("a", "Aka");
So, this time, the result will be:
1
1
What happens when the substring is not found in LOCATE?
If the substring is not found using the MySQL LOCATE function, the result is 0:
1
SELECT LOCATE("mom", "Mamma mia!");
This returns:
1
0
Instead, if either of the two arguments (or both) is NULL, the result is NULL.
Why use a visual database client?
A visual database client such as DbVisualizer makes database management easier by allowing you to efficiently explore, visualize, and query data. It provides an intuitive interface for handling tables, navigating schema relationships, and writing or debugging SQL in a powerful built-in SQL editor. Other features that stand out are autocomplete, ER diagrams, and advanced data export tools. Try the Pro version with a 21-day free trial!
