A few points about the MySQL LIKE operator

• The LIKE operator is used to fetch records from the table data with the pattern.
• The LIKE operator is generally used with wildcards; % and _ i.e. percentage and underscore.
• The modulus (%) wildcard replaces one or more characters.
• The underscore replaces only one character.
• We use Like operator with the WHERE clause.
• The LIKE operator is generally used with the SELECT statement.
• You may also use it with the UPDATE, and DELETE statements for affecting specifics table rows based on the given pattern.

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;

For instance, these are a few LIKE usages:

• WHERE col1 LIKE ‘%a’
• WHERE col1 LIKE ‘%a%’
• WHERE col1 LIKE ‘ab%’
• WHERE col1 LIKE ‘a_’
• WHERE col LIKE ‘_a_b_c’
• WHERE col1 LIKE ‘_a%’

The example of using LIKE with % wildcard

As mentioned earlier, the modulus (%) wildcard replaces one or more characters as using with the LIKE operator. On the other hand, 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 % 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 used 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 employees 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 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:

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 name ends at 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 letter n.