LIKE operator is used in the WHERE clause to filter results by pattern matching. LIKE operator uses 2 wildcard options. ‘%’ sign to match any number of characters(0,1, or more) and ‘_’ to match exactly 1 character.
Syntax:
LIKE '%abc%abc%'; LIKE '_abc';
Example:
Consider an employee table as shown below:
| id | first_name | last_name | department | salary | |
| 101 | bryan | walker | sales | 50,000 | bryan.walker@email.com |
| 102 | alex | carpenter | accounts | 55,000 | alex.carpenter@email.com |
| 103 | sam | white | sales | 60,000 | sam.white@egmail.com |
| 104 | john | barry | sales | 54,000 | john.barry@email.com |
| 105 | bill | wood | accounts | 65,000 | |
| 106 | robert | hopkins | support | 70,000 | |
| 107 | david | lee | support | 72,000 | david.lee@email.com |
| 108 | jack | sears | support | 65,000 | |
| 109 | tom | young | sales | 55,000 | tom.young@email.com |
Below is the SQL query to identify employees with invalid email addresses: The logic used here is if the ’email’ value does not contain ‘@’ it is considered invalid.
SELECT * FROM employee WHERE email not like '%@%';
Below is the SQL query to identify all employees with their first names starting with ‘a’
SELECT * FROM employee WHERE first_name like 'a%';
Below is the SQL query to identify all employees that have second character as ‘o’ in their first name.
SELECT * FROM employee WHERE first_name like '_o%';