LIKE ESCAPE: Search for a Literal Percent Sign

LIKE ESCAPE lets a pattern treat a wildcard character as literal text when that is the intended search. I distinguish the surrounding search wildcards from the character being sought. The escape character itself also needs an explicit literal representation.

Find a stored percent character

The first pattern uses ordinary percent wildcards around an escaped percent. The surrounding wildcards permit any preceding or following characters. The middle escape sequence asks for one literal percent character.

The query chooses an exclamation mark as its escape character. The ESCAPE clause declares that interpretation. Without that clause, the same pattern text would have a different meaning.

The first source value A%B expects LiteralPercent one. The ordinary AB source expects zero. The stored percent is content rather than a wildcard instruction in the searched value.

I return the original text beside the three indicators. That makes the literal target visible. The source string itself is not parsed as a LIKE pattern.

WITH Inputs AS
(
    SELECT CaseId,TextValue FROM (VALUES
        (1,CAST(N'A%B' AS nvarchar(12))),(2,CAST(N'AB' AS nvarchar(12))),
        (3,CAST(N'A_B' AS nvarchar(12))),(4,CAST(N'A!B' AS nvarchar(12))),
        (5,CAST(N'' AS nvarchar(12))),(6,CAST(NULL AS nvarchar(12)))
    ) AS v(CaseId,TextValue)
)
SELECT CaseId,TextValue,
    CASE WHEN TextValue IS NULL THEN NULL WHEN TextValue LIKE N'%!%%' ESCAPE N'!' THEN 1 ELSE 0 END AS LiteralPercent,
    CASE WHEN TextValue IS NULL THEN NULL WHEN TextValue LIKE N'%!_%' ESCAPE N'!' THEN 1 ELSE 0 END AS LiteralUnderscore,
    CASE WHEN TextValue IS NULL THEN NULL WHEN TextValue LIKE N'%!!%' ESCAPE N'!' THEN 1 ELSE 0 END AS LiteralEscape
FROM Inputs ORDER BY CaseId;

Underscore requires its own escaped pattern

The second pattern searches for a literal underscore using the same escape character. Its expected positive match is A_B. The percent-containing and ordinary-letter inputs do not satisfy it.

An unescaped underscore in a pattern represents a wildcard for one character. That would answer a broader question. Escaping it is therefore a semantic change rather than cosmetic formatting.

The example uses surrounding percent wildcards again because the target can appear inside a larger value. If the contract required an exact whole-field value, those surrounding wildcards would be inappropriate.

I keep percent and underscore checks separate. They identify different literal characters. A general user-text search would need to account for every pattern metacharacter it permits.

The chosen escape character can also appear as data

The third pattern uses two exclamation marks together to request one literal exclamation mark. Its expected positive match is A!B. That is different from using a single escape prefix before another character.

Choosing an escape character does not prohibit that character from the source data. It establishes how the pattern represents it. The source and pattern remain separate roles.

The empty-string row expects zero in all three indicators. It contains none of the target characters. The missing-source row deliberately returns NULL in all three indicators.

Those NULL indicators preserve the unknown comparison state. The CASE expressions do not classify a missing source as a definite nonmatch. A receiver can choose its missing-data policy separately.

Keep pattern construction within a defined search contract

These patterns are fixed literals rather than dynamically assembled input. That keeps their expected meanings independently reviewable. A real search interface needs an explicit rule for escaping supplied pattern text.

Parameterizing a value does not automatically turn LIKE wildcards into literal characters. The pattern still has wildcard semantics. A literal-search contract must handle that separate interpretation.

This example uses short Unicode strings and ordinary punctuation. It does not change database collation or language settings. Other matching requirements, including letter case, remain separate collation decisions.

The leading and trailing percent wildcards allow surrounding text. The escaped middle character is the literal target. Keep those roles separate when deciding whether the search means contains, starts with or equals.

When adapting the search, retain percent, underscore and the chosen escape character as separate source cases. Add empty and missing values. Confirm which characters are instructions in the pattern and which are data in the source.

Say which characters are instructions and which are data, and the search behaves.

A parameterized pattern is not a literal search, it is a pattern with wildcard rules.

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