MySQL Like Clause


MySQL like clause is used with where clause to fetch the records of matching pattern inside a character type data of a field. The general syntax of the like clause is
Syntax

select column1, column2.....
where like '%char_ _ _';

Wild Cards % and _ 9Underscore

  • The first wildcard % symbol replaces all the remaining characters of that field value including alphabet, digits, and special character not including itself.
  • Second wildcard _ ( underscore ) replaces only one character of that field only.

Sample DataBase to check Like Clause

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

Example of % wild cards

Below sql command is going to extarct all that rows where first character of the name is ‘p’ and after that it does not matter how many characters are there

mysql> select * from student
    -> where name like 'p%';
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
3 rows in set (0.00 sec)

The next command is going to display all the records where the last character is ‘t’.

mysql> select * from student
    -> where name like '%t';
+-------+--------+--------+-------+-------+-------------+--------+
| admno | name   | stream | marks | grade | phone       | gender |
+-------+--------+--------+-------+-------+-------------+--------+
| 102   | udit   | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit  | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat | human  | 67.50 | A     | 99887766551 | F      |
| 109   | mohit  | comm   | 64.80 | B     | 9673784651  | M      |
+-------+--------+--------+-------+-------+-------------+--------+
4 rows in set (0.00 sec)

Or you can display all those records that contains ‘i’ in their name.

mysql> select * from student
    -> where name like '%i%';
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+
6 rows in set (0.00 sec)

But suppose you want to find out all those names whose name length is only 5 char long and its name start with alphbet ‘p’

mysql> select * from student
    -> where name like 'p_ _ _ _';
+-------+-------+--------+-------+-------+------------+--------+
| admno | name  | stream | marks | grade | phone      | gender |
+-------+-------+--------+-------+-------+------------+--------+
| 111   | punya | comm   | 86.56 | B     | 0000000004 | F      |
+-------+-------+--------+-------+-------+------------+--------+
1 row in set (0.00 sec)

NOTE: Here you are supposed to use 4 _(underscore) to replace one character at a time.

You can also combine both wildcard. Suppose you want to display all those records whose name where the second last character of the name is ‘i’

mysql> select * from student
    -> where name like '%i_';
+-------+-------+--------+-------+-------+------------+--------+
| admno | name  | stream | marks | grade | phone      | gender |
+-------+-------+--------+-------+-------+------------+--------+
| 102   | udit  | sci    | 56.50 | B     | 0000000000 | M      |
| 103   | mohit | bio    | 46.50 | C     | 0000000001 | M      |
| 109   | mohit | comm   | 64.80 | B     | 9673784651 | M      |
+-------+-------+--------+-------+-------+------------+--------+
3 rows in set (0.00 sec)
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.