SQL Server: Understanding The LIKE Operator and Its Wildcards used in SQL Server

The LIKE operator is usually used in the WHERE clause of a query to filter data based on a specific pattern. Although it can be used with any data type including numbers and dates, it is recommended to use it with string values.

The pattern here is the sequence of characters to be searched for in a column or expression that can contain multiple wildcards. The wildcard character makes the LIKE operator more flexible than the equal (=) and not equal (!=) string comparison operators. The types of wildcard characters that can be used with the like operator in SQL Server are:

• %   - Match any string of any length (including 0 sizes of length)
• _   - Match one single character
• []  - Match any characters in the brackets, e.g. [xyz]
• [^] - Match any character that is not in the brackets, e.g. [^xyz]

Further, let's get some examples using the item table below.

Like without Wildcard

The LIKE operator without the wildcard is the same as the equals operator (=), as shown in the example query below. In SQL Server the keywords included with the LIKE operator are not case sensitive, so uppercase and lowercase letters are the same.

Like with Wildcard (%)

Only Wildcard % without any other characters will display all data.

SELECT * FROM item WHERE categorycode LIKE '%'

Like with Wildcard (%) as Suffix

If we add % at the end of a keyword, it will display data that begins with that keyword and can be followed by any character, either blank, 1 character, or many characters.

For example, we'll try to find item names starting with the word ultra. The keyword here is not case-sensitive, so uppercase and lowercase letters are considered the same.

SELECT * FROM item WHERE itemname LIKE 'Ultra%'

Like with Wildcard (%) as Prefix

If we put % at the beginning of the keyword, it will display data followed by the keyword and may start with any character, either blank, 1 character or many characters.

For example, the query below wants to find the name of an item that ends with the word gr. The word gr as a keyword here is also not case sensitive, so uppercase and lowercase letters are considered the same.

SELECT * FROM item WHERE itemname LIKE '%gr'

Like with Wildcard (%) as Prefix and Suffix

If the keyword is between 2 % wildcard then the query will do searching data that contains the keyword either at the beginning, middle or end.

For example, we will create a query that looks for data that contains the word AL. This keyword is also not case sensitive so uppercase and lowercase letters are considered the same.

SELECT * FROM item WHERE itemname LIKE '%AL%'

Like with Wildcard (%) between 2 Keywords

If we want to search a value with a certain starting character (prefix) and ending character (suffix) and any character in between then we add the % wildcard between the start and end keywords.

In the example below we will look for item names that start with the CH character and end with the GR character.

SELECT * FROM item WHERE itemname LIKE 'CH%GR'

Like with Underscore Wildcard  ( _ ) as Suffix

The underscore wildcard can be replaced by any character as many underscore typed, and the keyword character is at the beginning. 

For example, the following query is to find the category code with the prefix letter C and any 3 characters after. We give 3 underscores after the letter C.

SELECT * FROM item WHERE categorycode LIKE 'C___'

Like with Underscore Wildcard ( _ ) as Prefix

The underscore wildcard can be replaced by any character as many underscore typed, and the keyword character is following. 

For example, we'll filter text that ends with the number of "2" and 3 of any character at the beginning. We give 3 underscores before the "2".

SELECT * FROM item WHERE categorycode LIKE '___2'

Like with Underscore Wildcard ( _ ) between Prefix and Suffix Keyword

The next case is to find specific beginning characters and ending characters with any characters in between but limited to a specific number of characters.

For example, we'll find category code with prefix C and suffix 3 with any 2 characters in between.

SELECT * FROM item WHERE categorycode LIKE 'C__3'

Like with Underscore Wildcard  ( _ ) as Prefix and Suffix

If the keyword is between 2 underscore wildcard, it will filter value that contain keyword in the middle and any character to replace the underscore as many typed at the beginning and the end.

For example, we'll filter values that contain '00' in the middle, beginning with one of any character in the beginning, and one any character in the end.

SELECT * FROM item WHERE categorycode LIKE '_00_'

Like with Wildcard Square Bracket [ ]

The square bracket wildcard defines a list to match. It is almost like IN operator but this one considers every char in the list.

SELECT * FROM item WHERE itemid LIKE '[3,10]'

We can also add a keyword at the beginning as a fixed filter and define a list for its suffix. For example, we'll filter categorycode with C00 and suffix are 1 or 2.

SELECT * FROM item WHERE categorycode LIKE 'C00[1,2]'

We also can combine % with [] wildcard. By adding % data result will be more dynamic. For example, itemname with the first three letters is CHI, the next character is K or T, then followed by any character with no limit of length. The query would be:

SELECT * FROM item WHERE itemname LIKE 'CHI[K,T]%'

And here is the result:

Like with Wildcard Square Bracket that Excludes List [^ ]

Additional ^ in the brackets means to exclude values in the list. It means to exclude all data that match the list.

e.g.

SELECT * FROM item WHERE categorycode LIKE 'C00[^1,3]'