SQL LIKE Operator and Wildcards

Monday, November 14, 2011

tags SQL

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. You use LIKE in cases where you are unable to match an exact word or phrase. SQL wildcards can substitute for one or more characters when searching for data in a database.

Wildcards


% - substitute for zero or more characters
_ - subsitute exactly one character
[charlist] - subsitute any single character in charlist
[^charlist] - subsitute any single character not in the charlist
[!charlist] - subsitute any single character not in the charlist


Syntax


SELECT column_name(s)
FROM table_name
WHERE column_name LIKE '%pattern%'


Examples

Search for names that begin with 'Jo', such as 'John' or 'Joseph'.
SELECT * FROM Customers where firstName LIKE 'Jo%'

Search for names that end in 'm' such as Tim or Sam.
SELECT * FROM Customers where firstName LIKE '%m'

Search for names that contain 'il' such as Jill, William, and Phillip.
SELECT * FROM Customers where firstName LIKE '%il%'

Search for names that contain '_e_' such as 'Ted'.
SELECT * FROM Customers where firstName LIKE '_e_'

Search for names that contain the letters 'abc' such as 'Aldo, Bill, Cathy'.
SELECT * FROM Customers where firstName LIKE '[abc]%'

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook