Pattern Matching with text data

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.

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)