SQL ESCAPE

What does the SQL ESCAPE clause do?

Adds a custom single-character delimiter that treats wildcard symbols (% and _) as ordinary characters in LIKE or SIMILAR TO patterns.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ESCAPE: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, IBM Db2, Snowflake, Trino

SQL ESCAPE Full Explanation

ESCAPE appears after a LIKE or SIMILAR TO predicate and defines one character that turns the following character into a literal. Without ESCAPE, % matches any length string and _ matches a single character. By declaring an escape character, you can search for these symbols themselves (or other special tokens) inside text columns.The escape character must be a single, quoted character literal. When the database engine scans the pattern, any occurrence of the escape character causes the next character to be interpreted verbatim rather than as a wildcard. Only the specified character acts as the escape indicator; all other characters are evaluated normally.An ESCAPE clause is optional. If omitted, no escaping is available and wildcard symbols keep their special meaning. The clause is evaluated per expression, so different predicates can use different escape characters in the same query.Caveats:- The escape character cannot be a multi-byte string.- Choosing a character that also appears often in the data or pattern leads to verbose patterns and harder maintenance.- Some drivers automatically double the escape character when formatting strings; be sure to pass the exact pattern you intend.

SQL ESCAPE Syntax

<character_expression> LIKE <pattern> ESCAPE '<escape_character>'
<character_expression> SIMILAR TO <pattern> ESCAPE '<escape_character>'

SQL ESCAPE Parameters

escape_character (char(1)) - The single character that signals the next character should be treated literally

Example Queries Using SQL ESCAPE

-- Find emails containing the literal "_%" sequence
SELECT email
FROM users
WHERE email LIKE '%!_%' ESCAPE '!';

-- Search for paths that include "100%" exactly
SELECT file_path
FROM logs
WHERE file_path LIKE '%#100%%' ESCAPE '#';

-- Use ESCAPE with SIMILAR TO
SELECT code
FROM products
WHERE code SIMILAR TO '%(A|B)!_%' ESCAPE '!';

Expected Output Using SQL ESCAPE

Each query returns only rows where the wildcard symbols in the pattern are matched literally, not as pattern operators
For example, the first query returns emails that actually contain an underscore

Use Cases with SQL ESCAPE

Searching for text that includes literal "%" or "_" characters
Matching file paths or URLs that contain wildcard symbols
Building user-supplied filters where the pattern itself may include wildcard tokens
Avoiding brittle workarounds like string concatenation or double-escaping

Common Mistakes with SQL ESCAPE

Using more than one character for the escape delimiter (only one is allowed)
Forgetting to quote the escape character
Reusing the escape character elsewhere in the pattern unintentionally
Assuming ESCAPE applies globally to the whole statement instead of per predicate

First Introduced In

SQL-92

Frequently Asked Questions

How do I escape a percent sign in a LIKE pattern?

Use an ESCAPE clause with a chosen delimiter: `WHERE name LIKE '%!%%' ESCAPE '!'` treats `%` as a literal.

Can the escape character be longer than one character?

No. The SQL standard permits only a single character. Multi-character escapes raise a syntax error.

Does ESCAPE work with SIMILAR TO?

Yes. Append `ESCAPE ''` after the SIMILAR TO expression to override the default escape semantics.

Is ESCAPE case sensitive?

The escape character itself is not case sensitive, but the pattern comparison follows the column collation unless you use case-insensitive operators.