How to Perform Case-Insensitive LIKE Wildcard Searches in MySQL?

Case-Insensitive LIKE Wildcard Searches in MySQL

Problem:

Performing LIKE wildcard searches on a column can be case-sensitive, which can lead to missed results if the target string contains characters with different casing. How can we conduct case-insensitive LIKE wildcard searches?

Solution:

MySQL provides the LOWER() function, which converts strings to lowercase. By applying this function to both the column and the search pattern, we can ensure that the comparison is case-insensitive.

The following query demonstrates how to perform a case-insensitive LIKE wildcard search on the 'title' column of the 'trees' table:

SELECT * FROM trees WHERE LOWER(trees.title) LIKE '%elm%'

In this query, the LOWER() function is applied to the 'trees.title' column, effectively converting the column values to lowercase before performing the comparison. The '%elm%' wildcard pattern matches any title containing the substring 'elm' in any case.

This approach ensures that the search results include rows with titles containing 'Elm', 'elm', 'ELM', and any other variation of casing.

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