How Are MySQL INSTR() and LIKE operator similar?

We can use both INSTR() function and LIKE operator to search or match a particular pattern and they return same result. It can be demonstrated from the following example of ‘Student’ table.

Example

Suppose we want to search name, which contains ‘av’ in it, from ‘Student’ table. We can use INSTR() function as follows −

mysql> Select Name from student where INSTR(name, 'av') > 0;
+--------+
| Name   |
+--------+
| Gaurav |
| Aarav  |
| Gaurav |
+--------+
3 rows in set (0.00 sec)

Now, for the same kind of search we can use LIKE operator as follows −

mysql> Select Name from student where Name LIKE '%av%';
+--------+
| Name   |
+--------+
| Gaurav |
| Aarav  |
| Gaurav |
+--------+
3 rows in set (0.00 sec)

Both the queries above return the same result.

Lakshmi Srinivas

Programmer / Analyst / Technician

Updated on: 20-Jun-2020

393 Views

Kickstart Your Career

Get certified by completing the course

Get Started