How to Use LIKE in Case-Insensitive Search

1. Case Insensitive Search Using LIKE in MySQL
2. Case Sensitive Search in MySQL
3. Regular Expressions in MySQL

This tutorial article will show you how to use the LIKE operator to search a column. We will show you how to properly use this to include all results that meet specific criteria, whether or not the values are upper or lower case.

Case Insensitive Search Using LIKE in MySQL

When searching a table, the LIKE operator is always used after WHERE when searching a table.

This is especially useful when searching a term you are unsure about, such as a prefix or pattern of letters, or whether you want to include multiple instances that meet a specific condition.

After a SELECT statement, specifying which column in a table you want to search, add the LIKE operator followed by the following wildcards:

• The % wildcard will look for values that start with a letter placed after the letter. Likewise, it will look for values that end with a letter if the % sign is placed before the letter.
• The underscore (_) wildcard is used to limit the search to one value after using the % sign.

You can use the above in different ways to produce different results. The below example showcases how to search for a term using both wildcards.

 CREATE TABLE car_models (
  brand TEXT NOT NULL,
  model TEXT NOT NULL
);
 
INSERT INTO car_models VALUES ('Toyota', 'Camry');
INSERT INTO car_models VALUES ('toyota', 'Corolla');
INSERT INTO car_models VALUES ('toyota', 'corolla');
INSERT INTO car_models VALUES ('Ford', 'Prefect');
INSERT INTO car_models VALUES ('Ford', 'fiesta');
 
SELECT * FROM car_models WHERE car_models.model LIKE '%cor%'

The above will return only the rows that contain the letters cor in that sequence. By starting and ending with the % sign, the search was restricted to words with letters in that order.

| brand |  model  |
|-------|---------|
|toyota | Corolla |
|toyota | corolla |

The search ignored capitalization and searched for only the values that met the criteria. You could also run the following statement, which returns all models that contain the letter c.

SELECT * FROM car_models WHERE car_models.model LIKE '%C%'

Output:

| brand |  model  |
|-------|---------|
|Toyota |  Camry  |
|toyota | Corolla |
|toyota | corolla |
|Ford   | Prefect |

The following returned all models with the letter c. However, if you want to be more specific, you can place the % sign only after the letter c to return models that start with c.

SELECT * FROM car_models WHERE car_models.model LIKE 'C%'

Output:

| brand |  model  |
|-------|---------|
|Toyota |  Camry  |
|toyota | Corolla |
|toyota | corolla |

Case Sensitive Search in MySQL

If you want a case-sensitive search, you can add BINARY in front of the LIKE operator, and the search will be case-sensitive.

SELECT * FROM car_models WHERE car_models.model LIKE BINARY 'c%'

Output:

| brand |  model  |
|-------|---------|
|toyota | corolla |

All models starting with a capital C were excluded from the search.

Regular Expressions in MySQL

It is worth learning how to use regular expressions for more specific searches. These include more special characters, like the % sign, allowing you to do more in a search.

It consists of the * sign, which allows for a scenario in which the value following it can appear multiple times or none at all. The + sign, on the other hand, specifies that the following character needs to appear at least once.

The REGEXP and REGEXP_LIKE() can also be used in MySQL to march specific patterns.