A few points about the MySQL LIKE operator

Syntax for using the LIKE operator

Following is the general way of using the LIKE operator.

With SELECT

SELECT * FROM tbl_name

WHERE col LIKE pattern;

With DELETE

DELETE FROM tbl_name

WHERE col LIKE pattern;

With UPDATE statement

UPDATE Table tbl_name

SET col1 = val1….

WHERE col LIKE pattern;

The example of using LIKE with ‘%’ wildcard

As mentioned earlier, the modulus (%) wildcard replaces one or more characters when using with the LIKE operator.

On the other hand, the underscore wildcard replaces only one character.

The example below uses LIKE operator in the SELECT statement with ‘%’ wildcard. For that, we have an employee table that stores basic employee information (for the demo only).

We will search the table by employee name and see how the % operator is used with LIKE:

The query:

SELECT *

FROM tst_employees

WHERE employee_name LIKE  'M%';

The result-set:

So, two queries in the above graphic; the first one fetches all rows from the employee table while the other uses the LIKE operator.

Using % towards both sides

In this example, the ‘%’ wildcard is used on both sides of the given search character.

The query should return all employee’s records whose name has the character ‘a’ in between:

The query:

SELECT * FROM tst_employees

WHERE employee_name LIKE '%a%';

Result:

An example of using underscore (_) as a wildcard

As such, the underscore wildcard (_) only replaces the single character, the example below uses it with the LIKE in the WHERE clause as follows:

The query:

SELECT employee_name, employee_salary, employee_join_date FROM tst_employees

WHERE employee_name LIKE 'M_ke';

The result:

A combination of both wildcards example

In this example, we will fetch the records by using both wildcards with the LIKE operator.

The query will get records where employee name characters are at least five:

SELECT employee_name, employee_salary, employee_join_date FROM tst_employees

WHERE employee_name LIKE 'J_%_%_%_';

The result-set:

Ending at specified letter example

The final example uses the ‘%’ wildcard and fetches those records where employee’s name ends at the letter ‘n’. See the query and result below:

SELECT ID, employee_name, employee_salary FROM tst_employees

WHERE employee_name LIKE '%n';

The result:

You can see three employee names ending at the letter n.