How to Perform a Case-Insensitive LIKE Search in MySQL?

Mastering Case-Insensitive Searches with MySQL's LIKE Operator

MySQL's LIKE operator is invaluable for pattern matching within database columns. However, its default case-sensitive nature can be problematic. This guide demonstrates how to perform efficient case-insensitive searches.

Consider a table named trees with a title column storing tree names. To find all trees containing "elm", a naive approach like this fails to capture all variations:

<code class="language-sql">SELECT * FROM trees WHERE trees.title LIKE '%elm%';</code>

This query is case-sensitive, missing entries with different capitalization (e.g., "Elm", "ELM"). The solution lies in using the LOWER() or UPPER() functions.

The LOWER() function converts the column value to lowercase before comparison, enabling case-insensitive matching:

<code class="language-sql">SELECT * FROM trees WHERE LOWER(trees.title) LIKE '%elm%';</code>

This revised query successfully retrieves records containing "elm", "ELM", or any other case variation. Equivalently, UPPER() converts to uppercase for the same effect. Choose the function that best suits your data and query needs.

The above is the detailed content of How to Perform a Case-Insensitive LIKE Search in MySQL?. For more information, please follow other related articles on the PHP Chinese website!