How can I search (case-insensitive) in a column using LIKE wildcard?

Enhancing search flexibility

To ensure case-insensitive matching, the LOWER() function is your good friend. Combining LOWER() with the LIKE operator and the % wildcard gives you the edge in solving your problems. Helper functions such as LOWER() or LCASE() expands SQL search flexibility:

-- SQL humor: The 'wind' of change is blowing through this query!
SELECT * FROM trees WHERE LOWER(name) LIKE LOWER('%wind%');


The above SQL statement ensures that 'WIND', 'Wind', or 'wind' will all match the pattern %wind%.

Collation in action

An interesting way to ensure case-insensitive search is to adjust column's collation setting. Here, UTF8_GENERAL_CI enables case-insensitive operation:

ALTER TABLE trees MODIFY COLUMN name VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
-- SQL humor: As a matter of COLLATE-ral damage, indexes might need to rebuild.


The _ci in SQL collations stands for case-insensitivity. Remember that changing a column's collation might trigger the need for rebuilding indexes on that column.

Per-query case sensitivity tweaks

On-the-fly changes to case sensitivity can be achieved through query-specific collation adjustments, leaving the column's main structure intact. This is the SQL equivalent of a "temporary tattoo":

SELECT * FROM trees WHERE name COLLATE utf8_general_ci LIKE '%wind%';


The performance angle

Applying functions like LOWER() and COLLATE comes with performance considerations. For larger datasets or indexed columns, creating indexed computed columns with lowercase values is an efficient tactic. Binary collations can expedite case-insensitive searches where the language allows. It's the SQL equivalent of "hitting two birds with one stone":

CREATE INDEX idx_lower_title ON books ((LOWER(title)));
-- SQL humor: This extra work up front will 'pay off indexed' later!
SELECT * FROM books WHERE LOWER(title) LIKE '%cat%';


Scaling code readability

For prolonged maintenance and easy perusal, subquerying and utilizing aliases can keep your SQL scripts organized:

SELECT book_info.*
FROM (
  SELECT LOWER(title) AS low_title, author, year_published
  FROM books
) AS book_info
WHERE book_info.low_title LIKE LOWER('%wind%');
-- SQL pun: An alias walks into a SQL script... and the productivity improved drastically!
