(opens in a new tab)

How to Escape % and _ in SQL LIKE Queries

October 20, 2026 by Chat2DB Team

LIKE has two wildcards: % matches any sequence of characters (including none) and _ matches exactly one character. That is convenient until the text you are searching for contains a % or _. A search for 50% finds "50 percent off" too, and a search for file_name also finds file-name and filexname.

The fix is escaping, but each database handles it a little differently. Some have a default escape character, some don't, and MySQL adds a layer of string-literal escaping on top. This guide covers PostgreSQL, MySQL, SQL Server, Oracle and SQLite, and shows how to escape user input safely in application code.

All examples use this data:

CREATE TABLE promos (id int, title varchar(50));
INSERT INTO promos VALUES
  (1, '50% off'),
  (2, '50 percent off'),
  (3, 'file_name.txt'),
  (4, 'filename.txt');

The problem in one query

SELECT id, title FROM promos WHERE title LIKE '%_name%';

You meant "contains _name", but _ matches any single character, so this returns both file_name.txt and filename.txt (where _ matched the e in file). Escaping tells the database to treat the next character literally.

The ESCAPE clause works everywhere

Standard SQL lets you choose an escape character per expression with ESCAPE. This is the one form that works in every database covered here:

SELECT id FROM promos WHERE title LIKE '%!_name%' ESCAPE '!';   -- 3
SELECT id FROM promos WHERE title LIKE '50!%%'    ESCAPE '!';   -- 1

Pick an escape character that is unlikely to appear in your data (!, #, ^ or \). To match the escape character itself, double it: '%!!%' ESCAPE '!' finds strings containing !.

If you write portable SQL, always use an explicit ESCAPE clause. The defaults below differ.

PostgreSQL

PostgreSQL uses backslash as the default escape character for LIKE and ILIKE:

SELECT id FROM promos WHERE title LIKE '50\%%';    -- 1
SELECT id FROM promos WHERE title LIKE '%\_%';     -- 3
SELECT id FROM promos WHERE title LIKE '%\\%';     -- strings containing a backslash

With standard_conforming_strings = on (the default since 9.1), a backslash in a normal string literal is just a character, so '50\%%' reaches LIKE exactly as written. Inside an E'...' escape string, backslashes are processed first and have to be doubled: E'50\\%%'.

Two more PostgreSQL details:

• A pattern that ends with the escape character is an error:

ERROR:  LIKE pattern must not end with escape character

• ESCAPE '' turns escaping off completely, so \ becomes an ordinary character.

When you just need "contains this exact text", you can skip patterns entirely:

SELECT id FROM promos WHERE strpos(title, '50%') > 0;        -- contains
SELECT id FROM promos WHERE starts_with(title, 'file_');     -- prefix (PostgreSQL 11+)

MySQL and MariaDB

MySQL also defaults to backslash, but there are two layers of escaping: the string literal and the LIKE pattern. MySQL's string parser leaves \% and \_ alone (so they reach LIKE intact), but turns \\ into a single backslash. That gives these results (tested on MySQL 8.4):

SELECT id FROM promos WHERE title LIKE '50\%%';     -- 1
SELECT id FROM promos WHERE title LIKE '%\_%';      -- 3
SELECT id FROM promos WHERE title LIKE '%\\%';      -- \\ -> \ -> escapes the final %: ends with '%'
SELECT id FROM promos WHERE title LIKE '%\\\\%';    -- contains a backslash

Matching a literal backslash takes four backslashes in the SQL text: two for the string literal, two for LIKE.

If the server runs with sql_mode including NO_BACKSLASH_ESCAPES, backslash is an ordinary character in strings and LIKE has no default escape character. Then '%\%' matches strings containing a backslash. Use an explicit ESCAPE clause if your code may run under either mode:

SELECT id FROM promos WHERE title LIKE '50|%%' ESCAPE '|';

Case sensitivity in MySQL comes from the column's collation. With the MySQL 8 default utf8mb4_0900_ai_ci, LIKE 'cafe' matches Café, because the collation ignores both case and accents. Use a _cs or _bin collation for exact matching:

SELECT id FROM promos WHERE title LIKE 'Café' COLLATE utf8mb4_0900_as_cs;

SQL Server

SQL Server has no default escape character, but it has a third option: brackets. Inside [...] a wildcard is literal:

SELECT id FROM promos WHERE title LIKE '50[%]%';     -- 1
SELECT id FROM promos WHERE title LIKE '%[_]name%';  -- 3
SELECT id FROM promos WHERE title LIKE '%[[]draft]%'; -- contains "[draft]"

Brackets are also a character set: [abc] matches one of a, b or c, [a-f] a range, and [^0-9] anything except a digit. That means a literal [ must be escaped too, as [[]. A closing ] on its own needs no escaping.

The ESCAPE clause works as well:

SELECT id FROM promos WHERE title LIKE '50!%%' ESCAPE '!';

Case sensitivity follows the column collation. The common default SQL_Latin1_General_CP1_CI_AS is case-insensitive and accent-sensitive.

Oracle

Oracle has no default escape character. Always use ESCAPE:

SELECT id FROM promos WHERE title LIKE '50\%%' ESCAPE '\';
SELECT id FROM promos WHERE title LIKE '%\_name%' ESCAPE '\';

Two Oracle-specific traps:

• An empty string is NULL in Oracle, so LIKE '' (or a bind variable holding '') matches nothing, not even empty values.
• Oracle LIKE is case-sensitive by default. For case-insensitive search use UPPER(title) LIKE UPPER(:term), or set NLS_COMP = LINGUISTIC with a _CI NLS_SORT.

SQLite

SQLite has no default escape character either:

SELECT id FROM promos WHERE title LIKE '50\%%';               -- no rows: \ is literal
SELECT id FROM promos WHERE title LIKE '50\%%' ESCAPE '\';    -- 1

SQLite's LIKE is case-insensitive for ASCII letters only. 'café' LIKE 'CAFÉ' is false, because é and É are outside ASCII. PRAGMA case_sensitive_like = ON makes it fully case-sensitive. For exact substring checks, instr(title, '50%') > 0 avoids patterns.

Summary of default behavior

You can try any of these patterns against sample values with the SQL LIKE Pattern Tester (opens in a new tab), which applies each database's escape and case rules and generates an escaped LIKE clause for literal text.

Escaping user input in application code

Search boxes are where unescaped wildcards cause real problems. A user who types % gets every row back, and a pattern like %a%b%c%d%... on a big table can be slow. Always do two things:

1. Escape the wildcards in the user's text.
2. Pass the result as a bind parameter, never by string concatenation. Parameters prevent SQL injection; escaping only controls what LIKE matches.

Escape the escape character first, then % and _, otherwise you double-escape your own backslashes:

def like_escape(text: str, esc: str = "\\") -> str:
    return (text.replace(esc, esc + esc)
                .replace("%", esc + "%")
                .replace("_", esc + "_"))
 
term = like_escape(user_input)
cur.execute(
    "SELECT id, title FROM promos WHERE title LIKE %s ESCAPE '\\\\'",
    ("%" + term + "%",),
)

In Python, '\\\\' produces the two characters \\ in the SQL text. On MySQL (with backslash escapes on) that literal is one backslash, which is what ESCAPE expects. On PostgreSQL, use ESCAPE '\' instead, because there the literal is not unescaped. Using a non-backslash escape character such as ! avoids that difference entirely and works the same on every database:

def like_escape(text: str) -> str:
    return text.replace("!", "!!").replace("%", "!%").replace("_", "!_")
 
cur.execute(
    "SELECT id, title FROM promos WHERE title LIKE %s ESCAPE '!'",
    ("%" + like_escape(user_input) + "%",),
)

For SQL Server, also escape [ (as ![ with ESCAPE '!', or [[] with brackets).

PostgreSQL can do the escaping in SQL, which is handy in functions:

SELECT id, title
FROM promos
WHERE title LIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%';

Performance notes

• A pattern with a leading wildcard ('%term%') cannot use a normal B-tree index. For substring search on large tables, use a trigram index in PostgreSQL (pg_trgm), a FULLTEXT index in MySQL, or full-text search in SQL Server.
• A prefix pattern ('term%') can use a B-tree index. In PostgreSQL with a non-C collation, the index needs text_pattern_ops (or varchar_pattern_ops) for this to work.
• Escaped wildcards do not hurt index usage: 'file\_%' is still a prefix search.

Wrap-up

Escaping % and _ takes one character, but the rules vary: PostgreSQL and MySQL default to backslash, SQL Server, Oracle and SQLite need ESCAPE (or brackets in SQL Server), and MySQL adds its own string-literal escaping on top. For portable code, use an explicit ESCAPE '!' and escape user input before binding it.

If you switch between databases a lot, Chat2DB (opens in a new tab) lets you run the same search against PostgreSQL, MySQL, SQL Server, Oracle and SQLite connections side by side, and its AI assistant can convert a LIKE filter from one dialect's escaping rules to another's.