MySQL like operator – 5 examples with wildcards

The Like operator in MySQL

MySQL Like operator is used to search table columns data. It uses ‘_’ and ‘%’ wildcards to search in tables. The % (modulus) wildcard substitutes zero or more characters at any side of the search term. While _ (underscore) wildcard substitutes one character.

A like example with %

You can see examples of using these wildcards with Like in MySQL at the later part of this tutorial.

Like example with’_’

Also, you can use the Like operator to search not only text based columns data but also numeric columns as well.

Like operator Syntax

Following is the syntax to use MySQL Like with both wildcards.

Using Like with % (modulus) wildcard

Select * from table_name

Where col_name like ‘abc%’

Using Like with _  (underscore) wildcard

Select * from table_name

Where col_name like ‘_abc_’

You can see in the above syntax that wildcards % and _ can be used at either side of search character. To make it clearer look at the following examples of using Like with wildcards.

Like operator examples

For our examples to explain the like operator, we have a table, tbl_employee where we also entered some test data. This is how our test table is created.

You can see the table is created with five columns.

Using Like and %  example

Following example uses % operator with MySQL Like operator. In that example, we will place % wildcard after the search character. That means the Like query will return the rows for given column that starts with letter ‘M’. Click on the link to see the output of the query as well.

The Like query of MySQL:

Like and % wildcard at ending letter

Placing % wildcard before search string or character will return records that end with the given character, found in the specified column. The query and output are shown below:

Using % at both sides example

In this example. we will use % wildcard at both sides of the search term. That means the Like query will search the specified column data and return all rows where search term found in the text. To make it clearer see the following query and click on the link to see the output of the query.

The MySQL Like query with % at both sides:

Like and % with numeric search example

As mentioned earlier, you can also search numeric columns by using the Like operator with wildcards. The following example will return employees records whom salary is starting with 45.

The Like query for numeric column:

An example of Like with _ (underscore)

As explained in the earlier part of this tutorial, the underscore (_) wildcard substitutes single character in Like query. Following example shows how to use _.

The Like query with _:

As you can see it only fetched a row after substituting single character.

Was this article helpful?

Related Articles

Leave A Comment?