The SQL LIKE operator is used to find specific patterns or values within your data. With its wildcard characters and versatile syntax, you can craft queries that match your precise needs. Whether you are searching for names that start with a certain letter, addresses that contain a specific word, or numbers within a specific range, SQL Like has got you covered.
For this example we’ll use the Customer Table
You already know who to find specific email addresses by using SQL similar to:
select PhoneNumber, Email, LastName
from customer
where email = 'bbronw@lookout.com'But what if you want to find all email addresses ending in lookout.com?
To do this you can use the LIKE operator to perform pattern matching on column values. This following returns every email ending in lookout.com.
In SQL the LIKE operator returns a TRUE if the column value matches a pattern. Let’s look at some common LIKE patterns
SQL LIKE Patterns
| Wildcard Character | Description | Example |
|---|---|---|
| % | any string of zero or more characters. | where LastName like ‘%er’ returns all last names ending in er, such as Miller and Eminhizer. |
| _ (underscore) | a single character | where LastName like ‘_ood’ returns all four letter last names ending in ood, such as Hood and Wood. |
| [ ] | any single character within a specified range such as [q-z] | where LastName like ‘[abcd]%’ returns all names starting with either a,b,c, or d. Examples include Brown and Duffy. |
| [^] | any single character not within a specified range [^0-9] or [^0123456789] | where LastName like ‘[^abcd]%’ returns all names not starting with an a,b,c, or d. Examples include Miller and Wood. |
As you can see you can use more than one wildcard character in SQL like. You can do some pretty creative things.
SQL LIKE Operator Syntax
Here is the general syntax for using a LIKE operator in a WHERE clause.
SELECT column1, column2, ...
FROM TableName
WHERE ColumnName LIKE patternWhere
Column1, Column2, ...are the columns you want to retrieve data from.- TableName is the name of the table you want to retrieve data from.
ColumnName is the specific column you want to apply the pattern matching on.patternis the pattern you want to match. It can contain%as a wildcard to match any sequence of characters, and_to match any single character.
SQL LIKE – Find Phone Numbers
In this example assume all the phone numbers are entered similar to 249-124-4223. Let’s find all the phone numbers that have the correct format. We can do this by using LIKE with patterns that look for the correct numbers and dashes.
In this example we are assuming the area code, prefix, and suffix can not start with 0. Notice that the pattern tests each position in our phone number to make sure it is a number.
For example to test for the area code, which is the first three digits, we start the pattern off with [1-9][0-9][0-9].
Also notice we placed dashes (-) in the correct places to show how the number is separated.
SQL NOT LIKE
If we want to find all the phone numbers that incorrect we can use NOT to show those.
select PhoneNumber, Email, LastName
from customer
where PhoneNumber not like '[1-9][0-9][0-9]-[1-9][0-9][0-9]-[1-9][0-9][0-9][0-9]'Using LIKE with Conditional Operators
In this example lets find all Names ending in ‘r’ or ending in ‘o’
Exercise
See if you can find all customers that whose name starts with ‘B’ or ends in ‘r’
