Escaping wildcards while using LIKE predicate

When you look up the LIKE predicate on BOL you'll see the wildcard characters like %, _,[], [^] that will help finding a pattern in your tables. For example, the below sql will give you students whose lastname starts with D.

SELECT firstname, lastname
FROM students
WHERE lastname LIKE 'D%'

But things start getting interesting when your data has one or all of the above wildcards in your data and you would like to lookup a pattern. Lets create a table to illustrate this:

CREATE TABLE wildcards
(
  c1 int identity,
  c2 nvarchar(20)
);
--insert some data that has wildcards

INSERT INTO wildcards VALUES ('Joker')
INSERT INTO wildcards VALUES ('_Joker')
INSERT INTO wildcards VALUES ('Joker_')
INSERT INTO wildcards VALUES ('%Joker')
INSERT INTO wildcards VALUES ('%%Joker')
INSERT INTO wildcards VALUES ('[]Joker')
INSERT INTO wildcards VALUES ('[][]Joker')
INSERT INTO wildcards VALUES ('[^]Joker')
INSERT INTO wildcards VALUES ('__Joker')
GO
--check data,

SELECT * FROM wildcards

--9 rows

Now try a regular query with Like predicate and try to find all jokers that have an underscore preceding the value.

SELECT * FROM wildcards WHERE c2 LIKE '_%'

Surprise! SQL server returns all 9 rows. It thinks that we passed two wildcards. We need to tell SQL Server not to treat '_' as wildcard. You can do so using the ESCAPE keyword.

SELECT * FROM wildcards WHERE c2 LIKE '!_%' ESCAPE '!'
SELECT * FROM wildcards WHERE c2 LIKE '%!_' ESCAPE '!'