Beginner’s Guide To LIKE In SQL Server

Posted March 10, 2019 by Vishwanath Dalvi in Database, SQL Server

Beginner’s Guide To LIKE In SQL Server

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.

Like Operator WildCard Pattern In SQL Server

Like Operator WildCard Pattern In SQL Server

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, [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.

--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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply