MySQL Locate Function | Position of Substring in String

MySQL Locate is an inbuilt function in MySQL. It returns the position of the first occurrence of a substring in a string. However, if the substring does not exist then it returns 0. Moreover, the function performs a case-insensitive search (uppercase and lowercase characters are the same). In this article, we will discuss the MySQL Locate Function. Also, we will discuss a few examples of using it.

This function is an alias of MySQL Position Function.

Syntax

LOCATE (substring, string, start = 1)

Parameters

The MySQL Locate Function expects three parameters. However, two parameters are mandatory and one is optional. The description of the parameters is as follows:

• substring: The first parameter is the substring you want to search in the string.
• string: The second parameter is the string in which you need to search.
• start: The third and optional parameter is the start point. By default, the start point is 1.

Return Value

The MySQL Locate function returns the position of first occurrence of a substring in a string. However, if the substring is not present, then it returns 0.

Examples

Let’s discuss a few examples of using the function.

Example 1: Simple Usage

mysql> SELECT LOCATE("com", "concatly.com") as position;
+----------+
| position |
+----------+
|       10 |
+----------+

In the above example, we search for first occurrence of the substring “com” in “concatly.com”. The function returns the position of the first occurrence.

Example 2: Start Point

mysql> SELECT LOCATE("c", "concatly.com") as position;
+----------+
| position |
+----------+
|        1 |
+----------+

mysql> SELECT LOCATE("c", "concatly.com", 3) as position;
+----------+
| position |
+----------+
|        4 | 
+----------+

mysql> SELECT LOCATE("c", "concatly.com", 7) as position;
+----------+
| position |
+----------+
|       10 |
+----------+

We can pass a custom start point and the function performs the search after the starting point.

Example 3: Using With Where Clause

We can use the locate function along with the where clause. In the below example, we have three rows in the table. We search for rows which have “a” in the user_name field.

mysql> select user_name from user_description;
+-----------+
| user_name |
+-----------+
| chandler  |
| monica    |
| ross      |
+-----------+

mysql> select user_name from user_description where LOCATE("a", user_name) > 0;
+-----------+
| user_name |
+-----------+
| chandler  |
| monica    |
+-----------+

Conclusion

In conclusion, we discussed the MySQL Locate Function. You can learn more about it on Official MySQL Documentation. Also, you can learn about more MySQL String Functions on Concatly.