- 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 LOCATE() function can be used with MySQL WHERE clause?
When we use LOCATE() function with MySQL WHERE clause, we need to provide the substring as first argument and column name of the table as the second argument along with a comparison operator. Following is an example using ‘Student’ table to demonstrate it −
Example
Suppose we have the following values in ‘Student’ table −
mysql> Select * from Student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 15 | Harshit | Delhi | Commerce | | 20 | Gaurav | Jaipur | Computers | | 21 | Yashraj | NULL | Math | +------+---------+---------+-----------+ 5 rows in set (0.02 sec)
Now, following query show how we can use LOCATE() function with WHERE caluse
mysql> Select Name, LOCATE('av',name)As Result from student where LOCATE('av',Name) > 0; +--------+--------+ | Name | Result | +--------+--------+ | Gaurav | 5 | | Aarav | 4 | | Gaurav | 5 | +--------+--------+ 3 rows in set (0.00 sec) mysql> select name, LOCATE('av',name)As Result from student where LOCATE('av',Name)=0 ; +---------+--------+ | name | Result | +---------+--------+ | Harshit | 0 | | Yashraj | 0 | +---------+--------+ 2 rows in set (0.00 sec)
Advertisements