How do you make LIKE match a literal % or _ character in SQL?

## The problem Inside a `LIKE` pattern, `%` and `_` are metacharacters. If the text you are actually looking for contains one of them, a naive pattern silently over-matches. `WHERE discount_label LIKE '%50%%'` does not mean "contains 50%" — the trailing `%` is just another wildcard, so it means "contains 50", and `'50 units'` comes back too. The worst case is `_`: `WHERE code LIKE '%_%'` matches every value with at least one character, which is usually the entire table. ## The ESCAPE clause The standard predicate is: ```sql <value> [NOT] LIKE <pattern> [ESCAPE <single character>] ``` The `ESCAPE` clause nominates one character. Inside the pattern, that character strips the special meaning from the character that follows it: ```sql SELECT * FROM products WHERE code LIKE 'AB!_%' ESCAPE '!'; -- matches 'AB_1', 'AB_XYZ'; does NOT match 'ABC1' ``` The escape character is followed by `%`, `_`, or itself. To search for the escape character literally, write it twice: with `ESCAPE '!'`, the pattern `'!!'` means a single literal `!`. The standard treats an escape character followed by anything else as an error; engines vary in how strictly they enforce that, so do not rely on other combinations. ## There is no default in standard SQL This is the fact interviewers probe. In standard SQL, if you omit `ESCAPE`, **no** character is an escape character — `%` and `_` are unconditionally wildcards. Engines diverge here: - **PostgreSQL** and **MySQL** treat a backslash as the escape character for `LIKE` even when the clause is absent. - **SQL Server** and **SQLite** have no default; you must write `ESCAPE` (T-SQL additionally offers bracket character classes such as `[%]`, but that is a dialect extension, not the portable answer). So the same pattern can behave differently on two engines. Writing `ESCAPE` explicitly removes the ambiguity, and it costs you one clause. ## Why backslash is a bad choice Backslash is the intuitive pick and the wrong one. In MySQL, backslash is *also* an escape character inside string literals by default, so the literal you type is processed twice and you end up doubling backslashes to get one through. Choosing a character that has no other meaning — `!`, `#`, `|` — and declaring it with `ESCAPE` sidesteps the whole layering problem. Pick one that cannot occur in the searched data, or escape it too. ## Escaping values that come from elsewhere When the pattern is assembled from a value you did not write — a search box, an imported list, a stored prefix — you must escape that value's own `%`, `_` and escape characters before concatenating it into the pattern. Order matters: escape the escape character **first**, then the wildcards, otherwise you re-escape the escape characters you just inserted and corrupt the pattern. Note this is a *correctness* concern, entirely separate from how you bind values into the statement. ## Worked comparison ```sql -- Wrong: the user's underscore is a wildcard WHERE code LIKE '%A_1%'; -- matches 'AB1', 'AX1', ... -- Right: the underscore is literal WHERE code LIKE '%A!_1%' ESCAPE '!'; -- matches 'A_1' only ``` A useful sanity check when debugging an over-matching filter: count the rows returned by the pattern with all wildcards removed. If the filtered count is suspiciously close to the table count, an unescaped `_` or `%` from the search text is usually the reason. ## Interaction with NOT LIKE `ESCAPE` attaches to the predicate, not to the pattern literal, and it works identically under negation: `WHERE code NOT LIKE '%!_%' ESCAPE '!'` returns the rows whose code contains no literal underscore. ## What an interviewer is listening for Three things: that you reach for `ESCAPE` rather than trying to "quote" the wildcard some other way; that you know standard SQL has no default escape character and engine defaults differ; and that you double the escape character to match it literally. A candidate who says "just use a backslash, it always works" has not been bitten by an engine where it does not.