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

With DELETE

With UPDATE statement

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:

The result-set:

MySQL LIKE

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:

Result:

MySQL LIKE wildcard

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:

The result:

MySQL LIKE underscore

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:

The result-set:

MySQL LIKE under_mod

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:

The result:

MySQL LIKE ending

You can see three employee names ending at letter n.