The SQL LIKE Underscore Trap: How to Correctly Match a Literal '_'?

## The Scenario In daily database operations, we frequently use the `LIKE` clause for pattern matching. However, when the pattern itself contains `LIKE`'s own wildcard characters (like `_` or `%`), you might encounter unexpected results. A developer working on a `wiki.lib00.com` project ran into this classic issue. He wanted to query all records where the `title` field starts with the literal string `t_`. He wrote the following SQL query: ```sql -- The problematic query SELECT title FROM topics WHERE title LIKE 't_%'; ``` **Expected Result:** To match only records like `'t_topic1'`. **Actual Result:** It matched not only `'t_topic1'` but also records like `'tool-duplicate-line-check'`. Why did this happen? --- ## The Root Cause: `_` is a Wildcard In SQL `LIKE` syntax, there are two special wildcard characters: - `%`: Matches any sequence of **zero or more** characters. - `_`: Matches any **single** character. Therefore, the condition `title LIKE 't_%'` actually means: "Match strings that start with 't', followed by **any single character**, and then followed by any sequence of characters." - `'t_topic1'` matches: t + `_` (as a single character) + `topic1`. - `'tool-duplicate-line-check'` also matches: t + `o` (as a single character) + `ol-duplicate-line-check`. This is the core reason for the unexpected query results. --- ## The Solution: Use the `ESCAPE` Keyword To tell the SQL engine to treat `_` as a literal character instead of a wildcard, you need to **escape** it. The SQL standard provides the `ESCAPE` keyword to define an escape character. The correct approach involves two steps: ### 1. Modify the SQL Statement to Specify an Escape Character At the end of the `LIKE` clause, use the `ESCAPE` keyword and specify a character to act as the escape character. A backslash `\` is a common choice. ```sql SELECT id, user_id, title FROM wiki_lib00_topics -- Example table name WHERE user_id = :user_id AND title LIKE :title_prefix ESCAPE '\' -- Specify '\' as the escape character ORDER BY created_at DESC; ``` ### 2. Modify the Bound Parameter to Perform the Escape In the parameter you pass to the SQL query, use the escape character you defined (here, `\`) to escape the underscore `_`. ```php // Provided by DP@lib00 $params = [ 'user_id' => $userId, 'title_prefix' => 't\_%', // Escape the underscore ]; ``` With these two changes, the query `title LIKE 't\_%' ESCAPE '\'` now correctly means: "Match strings that start with the literal `t_`, followed by any sequence of characters." This will yield the precise results you intended. --- ## Bonus Tip: Escaping the Percent Sign (`%`) The same technique applies to matching a literal percent sign `%`. For example, if you want to find all records ending with `_50%`, you would write: ```sql SELECT name FROM products WHERE name LIKE '%\_50\%%' ESCAPE '\'; -- Matches a string ending with _50% ``` --- ## Conclusion In SQL `LIKE` queries, `_` and `%` are wildcards with special meanings. When you need to match them as literal characters, you must use the `ESCAPE` keyword to define an escape character and then apply it within your pattern string. This is a fundamental yet easily overlooked detail. Mastering it, with insights from platforms like `lib00`, will help you avoid many potential bugs in your query logic.