What is the use of LOCATE() function in MySQL?


With the help of LOCATE() function, MySQL returns the position of the first occurrence of a substring in the given string. We must have to pass both the strings (i.e. substring, which is to be searched and the string, from which substring is to be searched) as arguments of the LOCATE() function.

Syntax

LOCATE(Substring, String)

In this function, Substring is the string whose position of occurrence needs to find and the string is a string from which the occurrence of substring needs to be searched.

Example

mysql> Select LOCATE('DE','ABCDEFGH');
+-------------------------+
| LOCATE('DE','ABCDEFGH') |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

mysql> Select LOCATE('G','ABCDEFGH');
+------------------------+
| LOCATE('G','ABCDEFGH') |
+------------------------+
|                      7 |
+------------------------+
1 row in set (0.00 sec)

mysql> Select LOCATE('GH','ABCDEFGH');
+-------------------------+
| LOCATE('GH','ABCDEFGH') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

As from the above examples, it can be observed that it returns the first occurrence of the substring within a string.

Sharon Christine
Sharon Christine

An investment in knowledge pays the best interest

Updated on: 20-Jun-2020

146 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements