LIKE operators and wildcards

LIKE operator is used with wild card characters and used to fetch the records that matches to LIKE query.

Following wild cards are used with LIKE operator.

% : Matches to zero/more characters

- : Matches to single character (or) number.

mysql> SELECT * FROM employee;
+----+---------+------+------------+---------------------+------------+
| id | name    | age  | salary     | mailId              | city       |
+----+---------+------+------------+---------------------+------------+
|  1 | Hari    |   23 | 13580.2370 | hari@hari.com       | Bangalore  |
|  4 | Manju   |   36 | 96433.5570 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 | 41800.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 | 96410.2370 | sankalp@sankalp.com | Chenai     |
|  8 | Prasob  |   37 | 13580.2370 | prasob@sandesh.com  | Trivendram |
+----+---------+------+-------

a. Get all employees whose city ends with e.

mysql> SELECT * FROM employee WHERE city LIKE "%e";
+----+--------+------+------------+-------------------+-----------+
| id | name   | age  | salary     | mailId            | city      |
+----+--------+------+------------+-------------------+-----------+
|  1 | Hari   |   23 | 13580.2370 | hari@hari.com     | Bangalore |
|  4 | Manju  |   36 | 96433.5570 | manju@sandesh.com | Bangalore |
|  5 | Rakesh |   26 | 41800.0000 | rakesh@hari.com   | Bangalore |
+----+--------+------+------------+-------------------+-----------+
3 rows in set (0.00 sec)

b. Get all employees whose city not ends with e.

mysql> SELECT * FROM employee WHERE city NOT LIKE "%e";
+----+---------+------+------------+---------------------+------------+
| id | name    | age  | salary     | mailId              | city       |
+----+---------+------+------------+---------------------+------------+
|  6 | Sankalp |   38 | 96410.2370 | sankalp@sankalp.com | Chenai     |
|  8 | Prasob  |   37 | 13580.2370 | prasob@sandesh.com  | Trivendram |
+----+---------+------+------------+---------------------+------------+
2 rows in set (0.00 sec)

c. Get all employees whose age is between 20 – 29 (inclusive).

mysql> SELECT * FROM employee WHERE age LIKE "2_";
+----+--------+------+------------+-----------------+-----------+
| id | name   | age  | salary     | mailId          | city      |
+----+--------+------+------------+-----------------+-----------+
|  1 | Hari   |   23 | 13580.2370 | hari@hari.com   | Bangalore |
|  5 | Rakesh |   26 | 41800.0000 | rakesh@hari.com | Bangalore |
+----+--------+------+------------+-----------------+-----------+
2 rows in set (0.00 sec)

d. Get all employees, where second character in the mail id is ‘a’.

mysql> SELECT * FROM employee WHERE mailId LIKE "_a%";
+----+---------+------+------------+---------------------+-----------+
| id | name    | age  | salary     | mailId              | city      |
+----+---------+------+------------+---------------------+-----------+
|  1 | Hari    |   23 | 13580.2370 | hari@hari.com       | Bangalore |
|  4 | Manju   |   36 | 96433.5570 | manju@sandesh.com   | Bangalore |
|  5 | Rakesh  |   26 | 41800.0000 | rakesh@hari.com     | Bangalore |
|  6 | Sankalp |   38 | 96410.2370 | sankalp@sankalp.com | Chenai    |
+----+---------+------+------------+---------------------+-----------+
4 rows in set (0.00 sec)

Previous                                                 Next                                                 Home