What is the LIKE operator in SQL?
The SQL LIKE operator is used to find matches between a character string and a specified pattern. This operator is most commonly used in conjunction with two other SQL operators, WHERE and SELECT, to search for a value in a column.
A wildcard is the pattern that specifies the search criteria. The SQL LIKE operator uses two wildcards, either independently or in conjunction.
These wildcards are:
- Percentage symbol (
%), represents zero, one, or multiple characters. - Underscore symbol (
_), represents a single character.
Syntax
Example
We will be using the following employees table as our example:
CREATE TABLE employees(emp_id int,emp_name varchar(20),designation varchar(20),age int,addr varchar(50));
We have input the following data into our table:
| emp_id | emp_name | designation | age | address |
|---|---|---|---|---|
| 1 | John | Product Manager | 25 | 31 Apple Street, London |
| 2 | David | Intern | 30 | 10 Banana Way, London |
| 3 | Mike | Branch Manager | 29 | 55 Mango Building, Dublin |
| 4 | Alex | General Manager | 36 | 415 Block AA, Lahore |
| 5 | Tom | Human Resources | 40 | 112 Sector X, Tokyo |
| 7 | Ryan | Designer | 19 | 47 Peach Street, Milan |
SELECT emp_name, age FROM employeesWHERE age LIKE '3%';
WHERE age LIKE '3%'; finds all values in the age column that start with 3. In this case, David and Alex are returned both who’s age begins with 3.
SELECT emp_name, designation FROM employeesWHERE designation LIKE '%Manager';
WHERE designation LIKE '%Manager'; returns all the records in which the values in the designation column end with ‘Manager’.
SELECT emp_name, addr FROM employeesWHERE addr LIKE '%street%';
WHERE addr LIKE '%street%'; finds all values that have ‘street’ in any position.
SELECT emp_name FROM employeesWHERE emp_name LIKE '_o%';
WHERE emp_name LIKE '_o%'; finds values in the emp_name column which have the character ‘o’ in the second position. In this example, John and Tom are returned.
We can use any combination of the underscore and percentage wildcards to tailor our search criteria.
