We will now learn at how to match text patterns using the where clause and the LIKE operator in this section of the MySQL reference guide.
The equal to(=) comparision operator helps is selecting strings that are identical. Thus, to list the names of employees whose first names are John, we can use the following SELECT statement.
select f_name, l_name from employee_data where f_name = "John";
+——–+————+
| f_name | l_name |
+——–+————+
| John | Hagan |
| John | MacFarland |
+——–+————+
2 rows in set (0.00 sec)
What if we wanted to display employees whose first names begin with the alphabet J?
SQL allows for some pattern matching with string data. Here is how it works.
select f_name, l_name from employee_data where f_name LIKE "J%";
+——–+————+
| f_name | l_name |
+——–+————+
| John | Hagan |
| John | MacFarland |
| Joseph | Irvine |
+——–+————+
3 rows in set (0.00 sec)
You'll notice that we've replaced the Equal To sign with LIKE and we've used a percentage sign (%) in the condition.
The % sign functions as a wildcard (similar to the usage of * in DOS and Linux systems). It signifies any character. Thus, "J%" means all strings that begin with the alphabet J.
Similarly "%S" selects strings that end with S and "%H%", strings that contain the alphabet H.
Okay, let's list all the employees that have Senior in their titles.
select f_name, l_name, title from employee_data
where title like '%senior%';
+——–+——–+—————————-+
| f_name | l_name | title |
+——–+——–+—————————-+
| John | Hagan | Senior Programmer |
| Ganesh | Pillai | Senior Programmer |
| Kim | Hunter | Senior Web Designer |
| Mike | Harper | Senior Marketing Executive |
+——–+——–+—————————-+
4 rows in set (0.00 sec)
Listing all employees whose last names end with A is very simple
mysql> select f_name, l_name from employee_data
where l_name like '%a';
+——–+——–+
| f_name | l_name |
+——–+——–+
| Manish | Sharma |
| Alok | Nanda |
| Arthur | Hoopla |
+——–+——–+
3 rows in set (0.00 sec)
Assignments:
1.List all employees whose last names begin with P.
2.Display the names of all employees in the marketing division.
3.What will the following statement display
SELECT f_name, l_name, salary from
employee_data where f_name like '%k%';
4.List the last names and titles of all programmers
Possible Answers:
1.select l_name, f_name from employee_data where l_name like 'P%';
+——–+———+
| l_name | f_name |
+——–+———+
| Pillai | Ganesh |
| Pandit | Anamika |
+——–+———+
2 rows in set (0.00 sec)
2.select f_name, l_name from employee_data where title like '%marketing%';
+——–+——–+
| f_name | l_name |
+——–+——–+
| Mike | Harper |
| Monica | Sehgal |
| Hal | Simlai |
| Joseph | Irvine |
+——–+——–+
4 rows in set (0.00 sec)
3.The statement displays the first name, last name and salaries for all employees whose first names contain the alphabet k.
SELECT f_name, l_name, salary from employee_data where f_name like '%k%';
+———+——–+——–+
| f_name | l_name | salary |
+———+——–+——–+
| Anamika | Pandit | 90000 |
| Alok | Nanda | 70000 |
| Kim | Hunter | 110000 |
| Mike | Harper | 120000 |
+———+——–+——–+
4 rows in set (0.00 sec)
4.SELECT l_name, title from employee_data where title like '%programer%';
+————+———————–+
| l_name | title |
+————+———————–+
| Hagan | Senior Programmer |
| Pillai | Senior Programmer |
| Kruger | Programmer |
| MacFarland | Programmer |
| Sakamuro | Programmer |
| Nanda | Programmer |
| Rajabi | Multimedia Programmer |
| Simon | Multimedia Programmer |
| Hoopla | Multimedia Programmer |
+————+———————–+
9 rows in set (0.00 sec)