SQL Server LIKE
operator overview
The SQL Server
LIKE
is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE
operator is used in the WHERE
clause of the SELECT
, UPDATE
, and DELETE
statements to filter rows based on pattern matching.
The following illustrates the syntax of the SQL Server
LIKE
operator:Pattern
The pattern is a sequence of characters to search for in the column or expression. It can include the following valid wildcard characters:
- The percent wildcard (%): any string of zero or more characters.
- The underscore (_) wildcard: any single character.
- The [list of characters] wildcard: any single character within the specified set.
- The [character-character]: any single character within the specified range.
- The [^]: any single character not within a list or a range.
The wildcard characters makes the
LIKE
operator more flexible than the equal (=) and not equal (!=) string comparison operators.Escape character
The escape character instructs the
LIKE
operator to treat the wildcard characters as the regular characters. The escape character has no default value and must be evaluated to only one character.
The
LIKE
operator returns TRUE
if the column or expression matches the specified pattern.
To negate the result of the
LIKE
operator, you use the NOT
operator as follows:
SQL Server LIKE
examples
See the following
customers
table from the sample database:
The % (percent) wildcard examples
The following example finds the customers whose last name starts with the letter
z
:
The following example returns the customers whose last name ends with the string
er
:
The following statement retrieves the customers whose last name starts with the letter
t
and ends with the letter s
:The _ (underscore) wildcard example
The underscore represents a single character. For example, the following statement returns the customers where the second character is the letter
u
:
The pattern
_u%
- The first underscore character (
_
) matches any single character. - The second letter
u
matches the letter u exactly - The third character
%
matches any sequence of characters
The [list of characters] wildcard example
The square brackets with a list of characters e.g.,
[ABC]
represents a single character that must be one of the characters specified in the list.
For example, the following query returns the customers where the first character in the last name is
Y
or Z
:
The [character-character] wildcard example
The square brackets with a character range e.g.,
[A-C]
represent a single character that must be within a specified range.
For example, the following query finds the customers where the first character in the last name is the letter in the range
A
through C
:
The [^Character List or Range] wildcard example
The square brackets with a caret sign (^) followed by a range e.g.,
[^A-C]
or character list e.g., [ABC]
represent a single character that is not in the specified range or character list.
For example, the following query returns the customers where the first character in the last name is not the letter in the range
A
through X
:
The NOT LIKE
operator example
The following example uses the
NOT LIKE
operator to find customers where the first character in the first name is not the letter A
:
SQL Server LIKE
with ESCAPE
example
First, create a new table for the demonstration:
Second, insert some rows into the
sales.feedbacks
table:
Third, query data from the
sales.feedbacks
table:
If you want to search for
30%
in the comment
column, you may come up with a query like this:
The query returns the comments that contain 30% and 30USD, which is not what we expected.
To solve this issue, you need to use the
ESCAPE
clause:
In this query, the
ESCAPE
clause specified that the character is the escape character. It instructs the LIKE
operator to treat the %
character as a literal string instead of a wildcard. Note that without the ESCAPE
clause, the query would return an empty result set.
In this tutorial, you have learned how to use the SQL Server
LIKE
operator to check if a character string matches a specified pattern.
0 Comments