What’s the easiest way to match case insensitive text anywhere in a string?

For example, let’s say you’re a fruit company marketing executive and you want all records matching “orange”, including “navel oranges”, “Syracuse Orange,” “Orange is the New Black”, “The Real Housewives of Orange County”, “orange you glad I didn’t say banana”, “🍊” and so on.

Here’s a quick way to match case insensitive text.

Postgres & Redshift

Use ~*:

select *
from my_table
where item ~* 'orange';

The ~* operator searches for ‘orange’ anywhere in the string, thanks to the power of regular expressions.

MySQL

Use like:

select *
from my_table
where item like '%orange%';

In MySQL, like is case insensitive and much faster than regexp (the MySQL equivalent of ~* in Postgres and Redshift). Add the % wildcard to both sides of ‘orange’ for a substring match.

Learn more

• Regular Expressions Tutorial
• Pattern matching in Postgres
• Pattern matching in MySQL