Special Characters in a LIKE Pattern: Which Ones Are Wildcards

Special characters in a LIKE pattern can match the wrong rows, because three symbols mean more than themselves. Everything else you type is plain text. This post tests each common symbol on one small table. Then it shows how to search for the ones that fight back.

The Sample Table

Thirteen offers hold the symbols we want to test. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Offers (OfferID INT IDENTITY(1,1) PRIMARY KEY, OfferName VARCHAR(40) NOT NULL);
INSERT INTO Offers (OfferName) VALUES
  ('10% Off Tea Sampler'), ('50% Off Garden Books'), ('B_Day Book Bundle'),
  ('X_Mas Card Pack'), ('Spring_Seed Kit'), ('Plant Care Kit'),
  ('Tea [Sampler] Box'), ('Save $5 [Today] & Smile!'), ('Win-Win Pack (2 for 1)'),
  ('Cat^Dog Toys #4'), ('Reader''s Choice'), ('Vol. 2 Reading Pack'),
  ('Pick * or ? or / or \ or @');
GO

Which Symbols Are Special

Here’s a test that works for any symbol. CHARINDEX never treats a symbol as special, so it counts the rows that contain it. LIKE counts the rows it finds with the same symbol. If the two counts match, the symbol is plain text.

DECLARE @c TABLE (Sym CHAR(1) NOT NULL);
INSERT INTO @c VALUES ('%'), ('_'), ('['), (']'), ('^'), ('-'), ('@'), ('#'), ('$'), ('&'), ('.'), ('!'), ('('), (')'), (''''), ('/'), ('\'), ('*'), ('?');
SELECT c.Sym,
  (SELECT COUNT(*) FROM Offers o WHERE CHARINDEX(c.Sym, o.OfferName) > 0) AS RowsWithIt,
  (SELECT COUNT(*) FROM Offers o WHERE o.OfferName LIKE '%' + c.Sym + '%') AS RowsLikeFinds
FROM @c AS c;

Only three symbols disagree. The percent sign found all 13 rows, although 2 contain one. The underscore found 13, although 3 contain one. A lone open bracket found none, although 2 contain one.

Every other symbol matched itself, including the star and the question mark. Those two are wildcards in other tools, but not in a T-SQL LIKE pattern.

Percent Sign and Underscore

A pattern of three percent signs holds three wildcards and no percent sign. It matches every row.

SELECT COUNT(*) AS MatchedRows FROM Offers WHERE OfferName LIKE '%%%';

Wrap a wildcard in square brackets and it becomes plain text. This query finds the two offers with a real percent sign.

SELECT OfferName FROM Offers WHERE OfferName LIKE '%[%]%' ORDER BY OfferID;

The underscore works the same way. In the first query below, the first underscore is a wildcard for any one character. The bracketed one is a real underscore. The order matters: the second query asks for an underscore first and finds nothing.

SELECT OfferName FROM Offers WHERE OfferName LIKE '_[_]%' ORDER BY OfferID;

SELECT OfferName FROM Offers WHERE OfferName LIKE '[_]_%' ORDER BY OfferID;

SELECT OfferName FROM Offers WHERE OfferName LIKE '%[_]%' ORDER BY OfferID;

The first query returns B_Day Book Bundle and X_Mas Card Pack. The second returns no rows, and the third finds all three offers with an underscore anywhere.

Searching for a Bracket

The open bracket starts a set, so a lone one finds nothing. Put it inside its own set: [[]. A close bracket is plain text until a set is open, so ] needs no help.

SELECT OfferName FROM Offers WHERE OfferName LIKE '%[[]%' ORDER BY OfferID;

SELECT OfferName FROM Offers WHERE OfferName LIKE '%]%' ORDER BY OfferID;

Both queries return Tea [Sampler] Box and Save $5 [Today] & Smile!. The same rule explains a common surprise. A reader asked why a search for [MA].[Employee] failed. This table holds two lines of text to show it.

CREATE TABLE Snippets (Line VARCHAR(60) NOT NULL);
INSERT INTO Snippets (Line) VALUES ('SELECT * FROM [MA].[Employee]'), ('SELECT * FROM MA.Employee');
GO

SELECT Line FROM Snippets WHERE Line LIKE '%[MA].[Employee]%';

SELECT Line FROM Snippets WHERE Line LIKE '%[[]MA].[[]Employee]%';

The first query returns the wrong line, SELECT * FROM MA.Employee. [MA] is a set that means M or A, so the text A.E matches. The second query returns the line with real brackets.

The Caret and the Hyphen

The caret and the hyphen are plain text outside a set. Each query below finds one offer.

SELECT OfferName FROM Offers WHERE OfferName LIKE '%^%';

SELECT OfferName FROM Offers WHERE OfferName LIKE '%-%';

Inside a set they change meaning. A caret first in the set means not, and a hyphen between two characters means a range. This query returns the two offers that don’t start with a letter.

SELECT OfferName FROM Offers WHERE OfferName LIKE '[^A-Z]%' ORDER BY OfferID;

SELECT OfferName FROM Offers WHERE OfferName LIKE '%[-]%' ORDER BY OfferID;

The second query puts the hyphen in a set of its own, so it’s plain text again. It returns Win-Win Pack (2 for 1).

A Space Counts

A space in a pattern is a character like any other. A space before the closing quote asks for a name that ends in a space.

SELECT OfferName FROM Offers WHERE OfferName LIKE '_[_]% ';

SELECT OfferName FROM Offers WHERE OfferName LIKE '_[_]%';

The first query returns no rows. The second returns the two offers with an underscore in the second place. A stray space is the quietest way to get an empty result.

ESCAPE Does the Same Job

You can also pick your own escape character. Put it before the symbol, and name it after the pattern.

SELECT OfferName FROM Offers WHERE OfferName LIKE '_!_%' ESCAPE '!';

That returns the same two offers as the bracket version. Use brackets for one symbol, and ESCAPE when the pattern gets crowded.

Clean Up

Remove the sample tables when you finish.

DROP TABLE Snippets;
DROP TABLE Offers;

What to Remember

Three symbols need care: the percent sign, the underscore and the open bracket. Wrap each in square brackets to search for it. The caret and the hyphen matter only inside a set. A space in the pattern is a real character.

To go further, read Escaping Wildcards in LIKE: Searching for % and _ Literally and Wildcard Basics in SQL Server: How LIKE Patterns Work. For speed, read Leading Wildcard LIKE Searches: Why They Scan and What Helps.

A pattern is not a search string, it is a small language with three special symbols.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.