SQL Server LIKE operator is used for pattern and wildcard text search. Using LIKE with WHERE clause we can filter out rows by either matching partial text or using a wildcard as a placeholder for a character. We can use a single character or multiple character search.
Possible use of LIKE operator is when we don’t know the exact text to search but have partial words. Using partial text or subset of text we can find the rows which could match our input.
Assume we have a list of movie names in a table and need to find the movies which have text “Harry”. Considerably possible that “Harry” word may appear anywhere and we don’t know the exact full movie names. We can use LIKE search pattern to find movie names having “Harry” word anywhere. In case we know that movie names start with “Harry” then we can instruct LIKE operator to look for the name at the beginning. Various options can be evaluated using LIKE pattern matching.
Let’s understand the different wildcard characters used along with the LIKE operator.
The following figure shows the use of each wildcard character used with LIKE.
Let’s walkthrough with various examples to understand each pattern and its uses. We are using movie table with different movie names as an example to do pattern matching with LIKE. First, create a movie table and populate it with a list of names as shown in the below example.
% (percent) Wildcard Character – Open Ended Pattern
If % (percent sign) is appended at the end of string then it will try to find all rows starting with input pattern. For example LIKE ‘T%’ will fetch all rows starting with T character.
If % (percent sign) is appended at the start of string then it will try to find all rows ending with input pattern. For example LIKE ‘%T’ will fetch all rows ending with T character.
If % (percent sign) appended to the beginning and end of search pattern then it will look for the pattern anywhere in the string. LIKE ‘%T%’ will fetch all rows where T character present anywhere.
1.1 – Following query matches all rows having movie name starting with T character.
1.2 – Following query matches all rows where movie name ending with word Club.
1.3 – Following query matches all rows having word Part anywhere in the name.
_ (Underscore) Wildcard – Single or Multiple Character Match
For instance, we only know a few characters of a word and want to try every possible character. We can broaden our search using ( _ ) underscore wildcard character along with % (percent) wildcard. We can use this _ (underscore) character as a wildcard for any single or multiple characters in a pattern match.
For example, LIKE ‘C_ub%’, this pattern would match movie name starting with Caub, Cbub, Ccub, Club, [email protected] and so on. Every possible character would be evaluated in place of underscore.
We can use _ (underscore) for multiple characters pattern match similarly. LIKE ‘C__b%’ – this pattern will find all movie names starting with Caab, Cabb, Club, [email protected]$b, etc.
2.1 – Following query matches, all rows having T as the first character and e as a third character. Trying every possible character instead of _ (underscore). This will look for Tae, Toe, Tme, The, etc.
2.2 – Following query matches, all rows having movie name where G is the first character and f is the 4th character while trying every possible character in 3rd and 4th position. In our case, it matches G_ _father.
[ ] (Bracket Wildcard) – Search for Range of Characters
We can use [ ] (Bracket) to specify a range of single characters. For an example, we need to find movie names starting with character D, E or F. We can specify these characters in the range [D-F] and SQL will expand this range to D, E or F.
3.1 – In the following query, we find all movie names which starts with character D, E or F. It will stop matching rows after F character.
3.2 – The following query uses a range of characters between A to D and matches Aie, Bie, Cie and Die pattern.
3.3 – The following query uses a range of characters between A to C and matches Aull, Bull, Cull looking for this pattern at the end of movie names.
^ (Caret Wildcard) – NOT Match By Character
Assuming we need to find all movie names which doesn’t start with specific characters. Using ^ (Caret) wildcard we can create a pattern which can neglect character in the specified range.
4.1 – Following query finds all movie names which doesn’t start with character T. Therefore movie name like Titanic, The Godfather is not returned by the query.
4.2 – Following query finds all movie names which doesn’t start with characters A, B or C. Thus it avoids Aie, Bie, Cie but matches Die.
Using All Wildcard Together in Queries
Above all, we have learned to use different wildcard characters. We can combine all of the wildcards in a single pattern to enhance our search.
5.1 – In the following query, we have combined all four wildcard characters. In brief.
[A-D] – Movie names starting with character A, B, C or D.
[ _ ] – Movie names where the second character is not known to us thus using an underscore to try every possible character.
[^f] – Movie names where the third character is not f.
As a result above pattern matches Die word and return Die Hard movie name.
You can try different wildcard as part of practice to return different results.
Using Escape Character with LIKE Operator
A particular situation when we have wildcard characters in text to search. Assume we have %, [ ] or ^ symbol in our records. We can’t use normal LIKE operator to easily find these symbols as these characters are special thus we need to use ESCAPE clause. Let’s assume we have a movie name with a % symbol in it. Using LIKE ‘%%%’ won’t work directly. We need to tell SQL Server to treat a % symbol as a regular character by specifying ESCAPE character.
Using LIKE ‘%\%%’ ESCAPE ‘\’ – Here we are using \ a different character in front of wildcard character so that SQL Server will treat % as a regular character, not a wildcard.
The following query using ‘\’ as an escape character before % so that SQL Server will treat % as a regular character instead of a wildcard. Using an ESCAPE clause to indicate ‘\’ is used as an escape character. We can use any other symbol instead of ‘\’ as an escape character.
6.1 – \ as ESCAPE Character To find % Symbol
6.2 – ! as ESCAPE Character To find % Symbol
6.3 – ! as ESCAPE Character To find [ Symbol
6.4 – \ as ESCAPE Character To find ] Symbol
As a result, we have learned to use LIKE operator in SQL Server with different wildcard characters. We can use this wildcard for single or multiple character search. Moreover, we have seen how to use all these wildcards in a single query. Also using ESCAPE character to search for special wildcard characters in text. If you like this post you may like Tech-Recipes Database archives posts.