Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How is it possible in MySQL to find the location of the first occurrence of a substring in a string?
MySQL LOCATE() function makes it possible to find the location of the first occurrence of a substring in a string. Following is the syntax of using it −
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.
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.
Example
mysql> Select LOCATE('Good','RAM IS A GOOD BOY')As Result;
+--------+
| Result |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)In the above example, the output is 10 because the first occurrence of substring ’good’ start from 10th position in the string ‘RAM IS A GOOD BOY’.
However, we can also manage the starting point of searching with the help of another argument representing position. The syntax of LOCATE() with position argument would be as follows −
LOCATE(Substring, String, position)
Example
mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY')As Result;
+--------+
| Result |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY',5)As Result;
+--------+
| Result |
+--------+
| 16 |
+--------+
1 row in set (0.00 sec)From the above result set, we can see the difference between using position argument. If we do not use it the search by default starts from the 1st position and when we specify ‘5’ as the position argument in our example it starts from 5th position.
276 Views
