Krivalar Tutorials SQL - LIKE Operator
- The LIKE operator is used to select the list of records based on the given specified pattern.
- There are two wildcard characters used with this LIKE operator.
- The wildcard characters are:
- Percentage(%) --- represents zero ,one or multiple characters
- Underscore( _ )--- represents one or a single character.
- Sometimes both symbols are used in combination.
Syntax
SELECT Column-name FROM Table-name WHERE Column-name LIKE pattern;
Example
Below is the demo student table from the Collegedb database.
mysql> select * from student; +--------+---------+------+-----------+ | RollNo | Name | Age | City | +--------+---------+------+-----------+ | 1 | Aruna | 18 | Chennai | | 2 | Varun | 19 | Bangalore | | 3 | Ara | 19 | Kerala | | 4 | Markdin | 18 | Mumbai | | 5 | Kannan | 20 | Kerala | | 6 | Kanika | 18 | Chennai | | 7 | Jose | 19 | Kerala | +--------+---------+------+-----------+ 7 rows in set (0.29 sec)
Following are showing the possible combination of pattern form written in the WHERE clause with examples.
Expression Meaning Query Statement WHERE Name LIKE ‘ %a ’ list all the records that end with “ a ” mysql> SELECT * FROM student WHERE Name LIKE '%a'; +--------+--------+------+---------+ | RollNo | Name | Age | City | +--------+--------+------+---------+ | 1 | Aruna | 18 | Chennai | | 3 | Ara | 19 | Kerala | | 6 | Kanika | 18 | Chennai | +--------+--------+------+---------+ 3 rows in set (0.08 sec)
WHERE Name LIKE ‘ a% ’ list all the records that start with “ a ” mysql> SELECT * FROM student WHERE Name LIKE 'a%'; +--------+-------+------+---------+ | RollNo | Name | Age | City | +--------+-------+------+---------+ | 1 | Aruna | 18 | Chennai | | 3 | Ara | 19 | Kerala | +--------+-------+------+---------+ 2 rows in set (0.00 sec)
WHERE Name LIKE ‘ %ar% ’ list all the records that have “ ar ”in any position mysql> SELECT * FROM student WHERE Name LIKE '%ar%'; +--------+---------+------+-----------+ | RollNo | Name | Age | City | +--------+---------+------+-----------+ | 1 | Aruna | 18 | Chennai | | 2 | Varun | 19 | Bangalore | | 3 | Ara | 19 | Kerala | | 4 | Markdin | 18 | Mumbai | +--------+---------+------+-----------+ 4 rows in set (0.00 sec)
WHERE student-name LIKE ‘ a_% ’ list all the records that have start with “ a ” and are having at least 2 characters in length. mysql> SELECT * FROM student WHERE Name LIKE 'a_%'; +--------+-------+------+---------+ | RollNo | Name | Age | City | +--------+-------+------+---------+ | 1 | Aruna | 18 | Chennai | | 3 | Ara | 19 | Kerala | +--------+-------+------+---------+ 2 rows in set (0.03 sec)
WHERE student-name LIKE ‘ _a% ’ list all the records that have “ a ” character in the second position mysql> SELECT * FROM student WHERE Name LIKE '_a%'; +--------+---------+------+-----------+ | RollNo | Name | Age | City | +--------+---------+------+-----------+ | 2 | Varun | 19 | Bangalore | | 4 | Markdin | 18 | Mumbai | | 5 | Kannan | 20 | Kerala | | 6 | Kanika | 18 | Chennai | +--------+---------+------+-----------+ 4 rows in set (0.13 sec)
WHERE student-name LIKE ‘ k%a ’ list all the records that have start with “ k ” and end with “ a ” mysql> SELECT * FROM student WHERE Name LIKE 'k%a'; +--------+--------+------+---------+ | RollNo | Name | Age | City | +--------+--------+------+---------+ | 6 | Kanika | 18 | Chennai | +--------+--------+------+---------+ 1 row in set (0.00 sec)
