How to Escape Underscores in MySQL LIKE Queries?

MySQL LIKE Query with Underscore

In SQL, the LIKE operator can be used to search for patterns within strings. However, when using LIKE with an underscore (_), special consideration must be taken.

The Issue

When using LIKE to search for a string containing an underscore, the underscore is treated as a wildcard. This means that the following query:

SELECT id FROM images WHERE img_path LIKE 'abc_%'

will return all rows in the 'images' table where the 'img_path' column starts with "abc," including rows with underscores (e.g., "abcde_1.jpg").

The Solution

To escape the special character and ensure that the LIKE operator matches only strings starting with "abc_," it is necessary to escape the underscore with a backslash (). The correct query is:

SELECT id FROM images WHERE img_path LIKE 'abc\_%'

Explanation

By escaping the underscore, the LIKE operator treats it as a literal character. This requires the 'img_path' column to start with "abc_" in order to match. As a result, only rows 1 and 2 are returned.

The above is the detailed content of How to Escape Underscores in MySQL LIKE Queries?. For more information, please follow other related articles on the PHP Chinese website!