Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the LIKE logical operator to match a pattern.
What is LIKE operator in SQL Server?
The SQL Server LIKE operator performs flexible pattern matching with the help of wild cards which eliminates the need to specify the exact or entire pattern. It can be used in SELECT, UPDATE or DELETE query and is an alternative to IN, = and !=.
Operation
The SQL Server LIKE operator follows the WHERE clause in a SQL query and returns those rows in the resultset which match the pattern specified. It is also possible to negate the effect of the LIKE operator by adding NOT (which makes it NOT LIKE) so that the query returns resultset which does not match the pattern specified. The different wild cards that can be used with the LIKE operator are discussed below.
- % (Percentage) – Percentage is used to match one or more characters in a pattern and can be specified at the beginning, end or in the middle of the pattern to match.
- _ (Underscore) – Underscore is used to match a single character in a pattern and can be specified at the beginning, end or in the middle of the pattern to match.
- [] Bracket – Bracket is used to match a single character from a list or range of characters and can be specified at the beginning, end or in the middle of the pattern to match.
- [^] Caret – Caret within bracket is followed by a list of characters or a range of characters to match a single character in the pattern negatively i.e. to check and ensure that the pattern evaluated does not contain that character(s) in the specified position.
- (!) Exclamation – Exclamation is used as escape character to match characters that are usually used as wild cards (i.e. all the characters mentioned so far) in a LIKE query. The ESCAPE character tells the LIKE operator to treat any wild card character specified after the escape character as a normal regular character in a pattern.
SQL Server LIKE operator Syntax
The basic syntax of SQL Server LIKE operator is as follows.
SELECT expressions FROM tables WHERE expression LIKE | NOT LIKE [pattern [ESCAPE escape_character]];
In this syntax,
- expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
- tables – one or more than one table from where you want to retrieve data.
- WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
- LIKE – Logical operator used to match a string pattern.
- NOT LIKE– Logical operator used to negatively match a string pattern.
- pattern – String consisting of either alphabet, number, special character (including wild card characters) or one or all of these.
- ESCAPE – Optional. Keyword used to specify the character used as escape character.
- escape character– Optional. The character used as escape character. Usually !.
SQL Server LIKE operator Examples
NOTE: Columns are also referred to as fields or attributes and the terms are used interchangeably.
Let us see how it works with all the wild cards and query types. Suppose we have a customer table with the below details in it. We will query it using LIKE to demonstrate the different usage scenarios.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% discount on the next purchase |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | @help, customer service |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | no comments.. |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |
1) SQL Server LIKE – % (percentage) wild card examples
Let us see how we can use the % symbol at different positions in a pattern for matching multiple characters using the customers table.
a) Percent at end example
The below query is an example of using % to list customer comments from the customers table which begin with the letter ‘w’.
SELECT * FROM customers WHERE customer_comment LIKE 'w%';
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
b) Percent at beginning example
The below query is an example of using % to list customers who have their email id’s in gmail i.e. email id’s which end in ‘gmail.com’.
SELECT * FROM customers WHERE customer_email LIKE '%@gmail.com';
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% discount on the next purchase |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | no comments.. |
c) Percent in the middle example
The below query is an example of using % to list customer email id’s which have ‘co.’ in them.
SELECT * FROM customers WHERE customer_email LIKE '%co.%';
The query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | @help, customer service |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
2) SQL Server LIKE – _ (underscore) wild card examples
Let us see how we can use the _ symbol at different positions in a pattern to match a single character using the customers table.
The below query is an example which uses _ to list customer names which have ‘a’ as the second character.
SELECT * FROM customers WHERE first_name LIKE '_a%';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |
3) SQL Server LIKE – [] (bracket) wild card examples
Let us see how we can use the [] symbol at different positions in a pattern to match a character from a list or range using the customers table.
a) Character list example
The below query is an example which uses [] to list customer names which begin with either an ‘a’, ‘c’ or ‘l’ as specified in the character list within bracket.
SELECT * FROM customers WHERE first_name LIKE '[acl]%';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% discount on the next purchase |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
b) Character range example
The below query is an example which uses [] to list customer names which begin with any character between the specified range of a to l (which includes 12 characters from the alphabet).
SELECT * FROM customers WHERE first_name LIKE '[a-l]%';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% dicounnt on the next purchase |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
4) SQL Server LIKE – [^] caret within bracket examples
Let us see how we can use the [^] symbol at different positions in a pattern to negatively match a character from a list or range using the customers table.
a) Character list example
The below query is an example which uses [^] to list customer names which do not begin with either an ‘a’, ‘c’ or ‘l’ as specified in the character list within bracket.
SELECT * FROM customers WHERE first_name LIKE '[^acl]%';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | @help, customer service |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | no comments.. |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |
b) Character range example
The below query is an example which uses [^] to list customer names which do not begin with any character between the specified range of a to l (which includes 12 characters from the alphabet).
SELECT * FROM customers WHERE first_name LIKE '[^a-l]%';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | @help, customer service |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | no comments.. |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 10% refund |
5) SQL Server LIKE –! Escape character example
Let us see how we can use the ! escape character to treat wild card characters as normal regular characters in a pattern search so that the resultset return rows with them in it.
The below query is an example where the percentage symbols at the beginning and end are wild cards for random pattern matching but the percentage after ! is to be treated as a normal character in the pattern search and returned in the resultset. The aim is to retrieve percentage values from the table. Likewise it can be used for _, ^, [ and ].
SELECT *
FROM customers
WHERE customer_comment LIKE ('%15!%%') ESCAPE '!';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% discount on the next purchase |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |
6) SQL Server LIKE – NOT LIKE example
Let us see how we can use NOT LIKE for negative pattern matching so that the resultset does not contain the pattern matched.
The below query is an example which uses the NOT LIKE operator to return rows from the customers table where the customer email id is not a gmail id.
SELECT * FROM customers WHERE customer_email NOT LIKE '%gmail.com';
The above query will generate the following output.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | @help, customer service |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | @help, please call me back |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |
7) SQL Server LIKE – UPDATE query example
Let us see one example of how we can use LIKE in an update query to modify records that match (or does not match if we use NOT LIKE) the pattern specified with LIKE.
The below query is an example which removes the customer comments for customers having yahoo email ids.
UPDATE customers SET customer_comment=NULL WHERE customer_email like '%yahoo%';
We can check the update by running below SELECT query that will display the update.
SELECT * FROM customers WHERE customer_email like '%yahoo%';
| customer_id | first_name | last_name | customer_email | customer_comment |
| 4 | Pete | Williams | myname__Wpete@yahoo.co.in | NULL |
| 5 | Casey | Kugelman | ckugel1999@yahoo.co.in | NULL |
8) SQL Server LIKE – DELETE query example
Let us see one example of how we can use LIKE in a DELETE query to delete records that match (or does not match if we use NOT LIKE) the pattern specified with LIKE.
The below query is an example which deletes records of customers from the customers table who have yahoo email ids.
DELETE FROM customers WHERE customer_email like '%yahoo%';
We can check the update by running below SELECT query which shows only 6 records after the deletion of 2.
SELECT * FROM customers;
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | alicia_alicia@gmail.com | I want a 15% discount on the next purchase |
| 2 | Indi | Rossi | indi4u@gmail.com | When is the next big sale? |
| 3 | Jack | Smith | js2-k@hotmail.com | Is there any sale next week? What % discount? |
| 6 | Lauren | Crow | lcc2k02@gmail.com | Where are the new product updates? |
| 7 | Stephen | Fleming | fire_stephen_01@gmail.com | no comments.. |
| 8 | Vanessa | May | vanessamayU@hotmail.com | Need a call back and 15% refund |