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.
Syntax
WHERE expression LIKE pattern [escape character]
Pattern
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.
USE tempdb; IF OBJECT_ID('Movie','U') IS NOT NULL DROP TABLE Movie; CREATE TABLE Movie ( Id INT IDENTITY(1,1) PRIMARY KEY ,Name VARCHAR(255) ,RunningTime INT ,Star TINYINT ); INSERT INTO Movie VALUES ('The Godfather', 175, 9) ,('Ragging Bull', 129, 8) ,('The Godfather: Part II',202,9) ,('Forrest Gump',142,8) ,('The Godfather: Part III',162,7) ,('Fight Club',139,8) ,('Exam',101,6) ,('Titanc',194,7) ,('The Dark Knight Rises',164,8) ,('Die Hard', 132, 8) ,('100% Truth Lies',100,5) ,('Godzilla Part [II]',90, 4);
1.
% (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.
--This will find movie names starting with T character. SELECT * FROM Movie WHERE Name LIKE 'T%';
Id Name RunningTime Star ----------- -------------------------- ----------- ---- 1 The Godfather 175 9 3 The Godfather: Part II 202 9 5 The Godfather: Part III 162 7 8 Titanc 194 7 9 The Dark Knight Rises 164 8 (5 rows affected)
1.2 – Following query matches all rows where movie name ending with word Club.
--This will find movie names ending with word Club. SELECT * FROM Movie WHERE Name LIKE '%Club';
Id Name RunningTime Star ----------- ------------ ----------- ---- 6 Fight Club 139 8 (1 row affected)
1.3 – Following query matches all rows having word Part anywhere in the name.
--This will find movie names having Part word anywhere. SELECT * FROM Movie WHERE Name LIKE '%Part%';
Id Name RunningTime Star ----------- ------------------------- ----------- ---- 3 The Godfather: Part II 202 9 5 The Godfather: Part III 162 7 12 Godzilla Part [II] 90 4 (3 rows affected)
2.
_ (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, C@ub 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, C@$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.
--This will find movie names having T and e as 1st and 3rd character. SELECT * FROM Movie WHERE Name LIKE 'T_e%';
Id Name RunningTime Star ----------- ------------------------- ----------- ---- 1 The Godfather 175 9 3 The Godfather: Part II 202 9 5 The Godfather: Part III 162 7 9 The Dark Knight Rises 164 8 (4 rows affected)
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.
--This will find movie names having G and F as 1st and 4rd character. SELECT * FROM Movie WHERE Name LIKE '%G__f%';
Id Name RunningTime Star ----------- ------------------------- ----------- ---- 1 The Godfather 175 9 3 The Godfather: Part II 202 9 5 The Godfather: Part III 162 7 (3 rows affected)
3.
[ ] (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.
--Movie name which start with either D, E of F character. SELECT * FROM Movie WHERE NAME LIKE '[D-F]%';
Id Name RunningTime Star ----------- -------------- ----------- ---- 4 Forrest Gump 142 8 6 Fight Club 139 8 7 Exam 101 6 10 Die Hard 132 8 (4 rows affected)
3.2 – The following query uses a range of characters between A to D and matches Aie, Bie, Cie and Die pattern.
--Movie name which start with either A,B,C or D. SELECT * FROM Movie WHERE NAME LIKE '[A-D]ie%';
Id Name RunningTime Star ----------- ------------ ----------- ---- 10 Die Hard 132 8 (1 row affected)
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.
--Movie name which ends with either A,B,C or D character word. SELECT * FROM Movie WHERE NAME LIKE '%[A-C]ull';
Id Name RunningTime Star ----------- -------------- ----------- ---- 2 Ragging Bull 129 8 (1 row affected)
4.
^ (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.
--Caret - Movie names which doesn't start with character T SELECT * FROM Movie WHERE NAME LIKE '[^T]%';
Id Name RunningTime Star ----------- -------------------- ----------- ---- 2 Ragging Bull 129 8 4 Forrest Gump 142 8 6 Fight Club 139 8 7 Exam 101 6 10 Die Hard 132 8 11 100% Truth Lies 100 5 12 Godzilla Part [II] 90 4 (7 rows affected)
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.
--Caret - Movie names which doesn't start with character A or B. SELECT * FROM Movie WHERE NAME LIKE '[^A-B]ie%';
Id Name RunningTime Star ----------- ----------- ----------- ---- 10 Die Hard 132 8 (1 row affected)
5.
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.
--Use of all wildcard characters together SELECT * FROM Movie WHERE NAME LIKE '[A-D]_[^f]%';
Id Name RunningTime Star ----------- ---------- ----------- ---- 10 Die Hard 132 8 (1 row affected)
6.
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
--\ as ESCAPE Character To find % Symbol SELECT * FROM Movie WHERE NAME LIKE '%\%%' ESCAPE '\';
Id Name RunningTime Star ----------- -------------------- ----------- ---- 11 100% Truth Lies 100 5 (1 row affected)
6.2 – ! as ESCAPE Character To find % Symbol
--! as ESCAPE Character To find % Symbol SELECT * FROM Movie WHERE NAME LIKE '%!%%' ESCAPE '!';
Id Name RunningTime Star ----------- -------------------- ----------- ---- 11 100% Truth Lies 100 5 (1 row affected)
6.3 – ! as ESCAPE Character To find [ Symbol
--! as ESCAPE Character To find [ Symbol SELECT * FROM Movie WHERE NAME LIKE '%![%' ESCAPE '!';
Id Name RunningTime Star ----------- -------------------- ----------- ---- 12 Godzilla Part [II] 90 4 (1 row affected)
6.4 – \ as ESCAPE Character To find ] Symbol
--\ as ESCAPE Character To find ] Symbol SELECT * FROM Movie WHERE NAME LIKE '%\]%' ESCAPE '\';
Id Name RunningTime Star ----------- -------------------- ----------- ---- 12 Godzilla Part [II] 90 4 (1 row affected)
Summary
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.
