JIYIK CN >

This tutorial article will show you how to use LIKEthe operator to search a column. We will show you how to use it correctly to include all results that meet certain criteria, regardless of whether the value is uppercase or lowercase.

LIKEUse case-insensitive search in MySQL

When searching a table, always WHEREuse LIKEthe operator after the .

This is particularly useful when searching for a term you're unsure of, such as a prefix or letter pattern, or if you want to include multiple instances that meet certain criteria.

After SELECTthe statement, to specify which column in the table to search, add LIKEthe operator, followed by the following wildcard characters:

• %The wildcard character will find values ​​starting with the letter that comes after the letter. Likewise, if %the symbol is placed before the letter, it will find values ​​ending with the letter.
• The underscore ( _) wildcard character is used to %limit the search to one value after the symbol.

You can use the above in different ways to produce different results. The following example shows how to use two wildcards to search for a term.

 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 only return rows that contain the letters in that sequence cor. By %starting and ending with the symbol, the search is limited to words that contain the letters in that order.

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

The search ignores case and searches only for values ​​that match the criteria. You can also run the following statement, which returns call models that contain the letters .

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

Output:

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

The following returns all cmodels that begin with the letters . However, if you want to be more specific, you can cplace %a symbol after just the letters to return cmodels that begin with .

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 to do a case-sensitive search, you can LIKEadd before the operator BINARYand the search will be case-sensitive.

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

Output:

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

All models beginning with an Cuppercase are excluded from the search.

Regular expressions in MySQL

It's worth learning how to use regular expressions to do more specific searches. These include more special characters, such as %the ampersand, allowing you to do more in your searches.

It *consists of the symbol, which allows the following situation, that the value following it can appear multiple times or not at all. On the other hand, +the sign indicates that the following character needs to appear at least once.

REGEXPand REGEXP_LIKE()can also be used in MySQL to perform specific patterns.