Where LIKE
The LIKE operator is used in a WHERE clause to search for a specific character pattern (text, numeric, date, mix, etc) in a column.
Where LIKE is often used when only a segment of the data item is known.
Where LIKE uses two wildcard match options the percent and underscore sign:
% The percent sign represents zero, one, or multiple characters
_ The underscore represents a single character
SQL Where LIKE syntax:
SELECT Column1, Column2, Column3
FROM Table_Name
Where Column1 LIKE 'Ta%’
Example:
Lets use the “Customers” Table to find all customers with the last name beginning with “j”.
| CustomerID | FirstName | LastName | Address | City | State | zip |
|---|---|---|---|---|---|---|
| 1 | Rob | Johnson | 1 York Rd | New York | NY | 10013 |
| 2 | Jessica | Simmons | 2 High St | Maimi | FL | 33101 |
| 3 | Tracy | Baker | 3 River Dr | Chicago | IL | 60633 |
| 4 | Tod | Williams | 4 Cabin Ln | Denver | CO | 80210 |
| 5 | Tammy | Wilson | 5 Peach St | Seattle | WA | 98121 |
| 6 | Mike | Jones | 6 Rice Rd | Los Angeles | CA | 90009 |
SELECT
CustomerID,
FirstName,
LastName,
Address,
City,
State,
zip
FROM Customer
WHERE LastName LIKE ‘j%’
Results:
| CustomerID | FirstName | LastName | Address | City | State | zip |
|---|---|---|---|---|---|---|
| 1 | Rob | Johnson | 1 York Rd | New York | NY | 10013 |
| 6 | Mike | Jones | 6 Rice Rd | Los Angeles | CA | 90009 |
Lets use the LIKE Statement to return all zip codes that contain the number “1”.
SELECT
CustomerID,
FirstName,
LastName,
Address,
City,
State,
zip
FROM Customer
WHERE zip LIKE ‘%1%’
Results:
| CustomerID | FirstName | LastName | Address | City | State | zip |
|---|---|---|---|---|---|---|
| 1 | Rob | Johnson | 1 York Rd | New York | NY | 10013 |
| 2 | Jessica | Simmons | 2 High St | Maimi | FL | 33101 |
| 4 | Tod | Williams | 4 Cabin Ln | Denver | CO | 80210 |
| 5 | Tammy | Wilson | 5 Peach St | Seattle | WA | 98121 |
Sample LIKE combinations:
LIKE ‘z%’ - Finds any value(s) that start with “z”
LIKE ‘%z’ - Finds any value(s) that end with “z”
LIKE ‘%z%’ - Finds any value(s) that have “z” in any position
LIKE ‘z__%’ - Finds any value(s) that start with “z” and are at least 2 characters in length
LIKE ‘a%z’ - Finds any value(s) that start with “a” and ends with “z”
LIKE ‘_z% - Finds any value(s) that have “z” in the second position
SQL Tutorial