SQL Server Essentials: Using the SQL LIKE Operator

The T-SQL LIKE operator is one of the most useful additions to the SQL SELECT statement. The latter retrieves a result set, and you can easily filter the results using equality operators and the WHERE clause to select certain specific conditions. But what about those times when you might want to select rows that are based on partial values? That’s where the SQL LIKE statement comes into play, and I’ll show you how to use it in this tutorial.

SQL LIKE Syntax

You can see the basic syntax of the LIKE operator in the following listing:

• First, you supply the expression that you want to match. This is typically a column name.
• Next, you can optionally specify NOT to make this expression work for all conditions that do not match. Using the NOT LIKE condition is not the most common way to use the LIKE operator.
• You then need to supply the pattern to match. This can be a literal or it can include wildcard characters that make the matching process much more flexible.
• This can optionally be followed by an Escape character that can be used to help search for wildcard characters.
• If any of the arguments isn’t a character string data type, SQL Server will convert it to a character string data type.

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

SQL LIKE and wildcard characters

Before we dive into details, you should know that SQL LIKE pattern matching can include regular characters as well as wildcard characters. Wildcard characters enable you to select a specific character string that matches the wildcard pattern.

For pattern matching, regular characters must match the characters specified in the character string. However, wildcard characters can match with any fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than the regular equality ( = and != ) string comparison operators.

The LIKE operator accepts four different types of wildcard characters. Each wildcard character performs a different comparison function, and you can learn more in the following table.

Wildcard character	Description
% (percent sign)	Any string of zero or more characters.
_ (underscore)	Any single character.
[ ]	Any single character within the specified range [a-f] or set [abcdef].
[^]	Any single character not within the specified range [^a-f] or set [^abcdef].

The LIKE operator returns TRUE if the match_expression matches the specified pattern. Let’s use some examples to show you how to use the SQL LIKE operator with the AdventureWorksLT2019 sample database.

Matching multiple wildcard characters

The % wildcard represents zero or more characters. The following query retrieves all rows from the ‘Person.Person’ table where the ‘LastName’ starts with the letter ‘A’.

USE AdventureWorksLT2019;

SELECT FirstName, LastName FROM SalesLT.Customer

WHERE LastName LIKE 'A%';

Single-character wildcard matching

The _ wildcard represents a single character. The following example query retrieves all rows from the ‘SalesLT.Product’ table where the ‘Name’ has exactly four characters of any type followed by a hyphen.

USE AdventureWorksLT2019;

SELECT ProductID, Name FROM SalesLT.Product

WHERE Name LIKE '____-%';

Wildcard matching for a set of characters

The [] wildcard represents any single character within the specified range or set. The following SQL statement retrieves all rows from the ‘SalesLT.Product’ table where the ‘Name’ starts with ‘B’, ‘C’, or ‘D’.

USE AdventureWorksLT2019;

SELECT Name FROM SalesLT.Product

WHERE Name LIKE '[BCD]%';

Wildcard matching for a range of characters

You can also use the LIKE clause to support range searches. This query retrieves the last names ending with ‘ee’ and beginning with any single character between the letters ‘C ‘ and ‘L’.

USE AdventureworksLT2019;

SELECT LastName, FirstName FROM SalesLT.Customer

WHERE LastName LIKE '[C-L]ee'

Excluding characters that are not part of the specified set

The [^] wildcard represents any single character not within the specified range or set. The following query retrieves all rows from the ‘SalesLT.Product’ table where the ‘Name’ does not start with ‘A’, ‘B’, or ‘C’.

USE AdventureworksLT2019;

SELECT Name FROM SalesLT.Product

WHERE Name LIKE '[^ABC]%';

Using multiple wildcard matching conditions

The LIKE clause can also be combined with multiple wildcards in a single query. The following query uses multiple SQL wildcards to retrieve all rows from the ‘Person.Person’ table where the ‘FirstName’ contains the letter ‘a’ and the ‘LastName’ has any character followed by ‘l’.

USE AdventureworksLT2019;

SELECT FirstName, LastName FROM SalesLT.Customer

WHERE FirstName LIKE '%a%' AND LastName LIKE '_l%';

Searching for wildcard characters

Wildcard matching adds a lot of power and flexibility to the LIKE operator. However, you might wonder how you might go about using LIKE to match one of these wildcard characters themselves.

To do this, you can enclose the wildcard character in brackets, which allows you to use a wildcard character as a literal character. You can see some examples in the following table.

Pattern	Search value
LIKE ‘%2[%]%’	2%
LIKE ‘%[_]m%’	_m
LIKE ‘%[ [ ]%’	[

• The first example matches the number 2 followed by the percent wildcard character.
• The second example matches the underscore character followed by the letter m.
• The third example is used to match square bracket characters.

The SQL LIKE statement enhances the SQL SELECT statement

You should now better understand how you can use the SQL LIKE operator to enhance the power and flexibility of the SQL SELECT statement. Here, I also showed you how to use the LIKE clause with various wildcard characters. Between matching multiple wildcard characters, performing single-character matching, matching a set of characters, or excluding characters from the results, you really have a lot of possibilities at hand.