Escaping Wildcards in LIKE: Searching for % and _ Literally

You search for AB_12, and ABX12 joins the results. Escaping wildcards makes a literal search mean what the user typed. Percent signs and brackets deserve the same treatment.

Decide Whether the Input Is a Pattern

I ask what the search box promises before fixing its LIKE expression. A pattern search intentionally accepts wildcards. A literal search treats them as ordinary characters.

The two features need different contracts. A parameterized query protects executable SQL text in both cases. It doesn't turn LIKE metacharacters into literal characters automatically.

The underscore matches one character. The percent sign matches a sequence of characters. A left bracket begins a bracket expression in SQL Server patterns.

Create the sample table in a disposable database. Its product codes deliberately contain these characters. The rows are test inputs rather than measurements from a production search.

An equality predicate needs no LIKE escaping. Use equality when the business wants an exact code. Use a safely built pattern when the business wants a literal substring or prefix.

CREATE TABLE dbo.LiteralLikeDemo(CodeText nvarchar(80) NOT NULL PRIMARY KEY);
INSERT dbo.LiteralLikeDemo VALUES
(N'AB_12'), (N'ABX12'), (N'Rate%'), (N'Rate100'), (N'Part[2]'), (N'Bang!Code');
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'AB_12';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText = N'AB_12';

Use Brackets for Escaping Wildcards in a Fixed Pattern

Square brackets can express a literal percent sign as [%]. They can express a literal underscore as [_]. A literal left bracket uses [[] in the pattern.

That approach is clear for a fixed expression written by a developer. It becomes harder to maintain for arbitrary input. A reusable escape function keeps the application convention in one place.

The following statements search the deliberately special sample values. Compare them with their unescaped forms on the same table. The additional matching rows explain the difference.

A right bracket outside a bracket expression is ordinarily literal. Don't assume every punctuation mark needs identical replacement. The left bracket is what opens the special expression.

Escaping wildcards preserves the input's meaning. It doesn't remove punctuation from the stored code. Removing punctuation would change the value you are trying to locate.

SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'AB[_]12';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'Rate[%]';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'Part[[]2]';

Choose One ESCAPE Character

The ESCAPE clause names a character that introduces a literal metacharacter. The sample uses an exclamation mark. The pattern then writes !_ for a literal underscore and !% for a literal percent.

The escape character itself needs escaping when it appears in user input. Write !! to search for one literal exclamation mark. That detail prevents a new special character from breaking the fix.

The next statements show literal percent and left bracket searches. The ESCAPE clause belongs on the LIKE predicate. Forgetting it changes how the pattern is interpreted.

I choose a convention that every application search path can share. Mixing bracket replacement in one path with another convention elsewhere creates debugging work. Consistency makes test cases reusable.

Don't end an assembled pattern with an unmatched escape introducer. It no longer describes the requested literal value correctly. Escape the user's escape characters before adding other escapes.

SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'Rate!%' ESCAPE N'!';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'Part![2]' ESCAPE N'!';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE N'Bang!!Code' ESCAPE N'!';

Centralize Escaping Wildcards in One Function

The function below escapes exclamation marks first. It then escapes percent, underscore and left bracket. Reversing that order would also escape the markers the function has introduced.

Its input limit leaves room for every character to expand. The output returns nvarchar(max) so intermediate replacement isn't truncated at a bounded limit. LIKE itself still has a pattern-size limit.

The later query uses a short application input. Enforce a permitted input length before building a pattern. The function's broad capacity isn't permission to send an unlimited search pattern.

CREATE FUNCTION starts its own batch. GO separates the later test statement. Run the function definition once in the disposable database.

A null input returns null through these replacements. Decide whether your application should reject it or treat it as an absent filter. Don't silently turn it into a search matching every row.

GO
CREATE FUNCTION dbo.EscapeLikeLiteralDemo(@Input nvarchar(1000))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Escaped nvarchar(max) = CONVERT(nvarchar(max), @Input);
    SET @Escaped = REPLACE(@Escaped, N'!', N'!!');
    SET @Escaped = REPLACE(@Escaped, N'%', N'!%');
    SET @Escaped = REPLACE(@Escaped, N'_', N'!_');
    SET @Escaped = REPLACE(@Escaped, N'[', N'![');
    RETURN @Escaped;
END;
GO
DECLARE @Input nvarchar(1000) = N'AB_12';
DECLARE @Pattern nvarchar(max) = N'%' + dbo.EscapeLikeLiteralDemo(@Input) + N'%';
SELECT CodeText FROM dbo.LiteralLikeDemo WHERE CodeText LIKE @Pattern ESCAPE N'!';

Add Only the Wildcards the Search Promises

For a literal substring search, add percent signs around the escaped input. For a literal prefix search, add one percent sign after it. For exact matching, use equality instead.

The outer wildcards are intentional query behavior. The escaped internal characters are user data. Keeping that distinction visible prevents an accidental broad search.

A leading percent sign usually prevents an ordinary leading-key seek. Escaping doesn't repair that access limitation. A prefix search gives an appropriate index a more useful boundary.

The next query demonstrates a literal prefix. It doesn't change the table or input. Inspect the actual plan on representative data when performance matters.

Which search behavior does the user expect when entering a complete code? An exact search and a substring search can return different valid results. Settle that requirement before comparing speed.

DECLARE @Prefix nvarchar(1000) = N'AB_';
DECLARE @PrefixPattern nvarchar(max) = dbo.EscapeLikeLiteralDemo(@Prefix) + N'%';
SELECT CodeText FROM dbo.LiteralLikeDemo
WHERE CodeText LIKE @PrefixPattern ESCAPE N'!'
ORDER BY CodeText;

Test Escaping Wildcards Alongside Parameters

Pass the constructed pattern as a parameter when using application SQL. Don't concatenate it into the executable statement text. LIKE escaping and SQL injection prevention solve different input problems.

Test percent, underscore, left bracket and the chosen escape character together. Test leading and trailing occurrences too. A replacement chain that handles one symbol can still mishandle combinations.

Also test empty input and boundary lengths. An empty substring pattern matches broadly. The application needs an explicit policy for that behavior.

I compare the searched input with the returned codes during testing. Collation still controls case and accent comparisons. Literal wildcard handling doesn't make those comparisons binary.

Escaping wildcards deserves one shared function and a small, clear test set. Keep the expected search behavior next to that test. The underscore wasn't wrong, it was doing the job you accidentally gave it.

Test combinations such as a literal exclamation mark immediately before an underscore. The replacement order must preserve both characters. A passing test for each symbol separately does not establish that combination.

Keep the escaped pattern separate from the original input in diagnostic records. The original expresses what the user wanted. The pattern explains what SQL Server was asked to match.

Related reading on this blog: Leading Wildcard LIKE Searches: Why They Scan and What Helps and The Intricacies of T-SQL String Comparison: LIKE VS '='.

A literal search is not an accidental pattern, it is input with deliberate escaping and matching rules.

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