How Can I Perform Case-Insensitive LIKE Wildcard Searches in MySQL?

Mastering Case-Insensitive Wildcard Searches in MySQL

Many developers encounter the need for case-insensitive searches. Standard MySQL LIKE wildcard searches are, by default, case-sensitive. This limitation can be problematic when searching for data that might be stored with varying capitalization.

Consider this example: A search for "elm" in the title column of a trees table:

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

This query only retrieves rows where "elm" is explicitly lowercase. Entries like "Elm" or "ELM" would be missed.

To achieve a case-insensitive search, leverage the LOWER() function. This function converts strings to lowercase, enabling matches irrespective of capitalization.

Here's how to perform a case-insensitive search for "elm":

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

This revised query will return all rows containing "elm" in the title column, regardless of whether it's uppercase, lowercase, or a mixture of both.

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