SQL Like operator

Following are the main points about the SQL Like operator:

  • The Like is an operator in SQL that is used to search columns data by the specified criteria.
  • The like operator uses the wildcard character(s) to search in a column.
  • The SQL wildcards are % (percentage) and _ (underscore).
  • The % is a wildcard that substitutes for zero or more characters whereas ‘_’ substitutes single character.
  • You can search text as well as numbers by using the Like SQL operator.
See Like with % example
Like with _ operator example

Syntax of the like operator

The general syntax of using the SQL like operator is as follows:

Like With %

Select * from table_name

Where col_name like ‘abc%’

Like With _

Select * from table_name

Where col_name like ‘abc_’

Where you can place ‘%’ or ‘_’ before or after the search character or string. 

Placing % before the given search term in like operator means any data found in the given column ending with ‘abc’ will be returned.

e.g. xyzabc, 123abc

Whereas placing the % after the given search term in the like operator means any data found in the given column starting with ‘abc’ will be fetched.

Example of using the Like operator

Let us show you examples of the Like operator with our created database and table. We will use the same database, test_db, and tbl_employee table to show a few examples of the like with wildcard.

Just to remind this is how our example table is created in test_db:

Example of Like with % wildcard at the starting letter

The following example shows how to use the % (a like wildcard) with the like operator to find the employees whom names are starting with the letter ‘M’.

The SQL query:

Demo table with Select Like

Example of Like with % wildcard at the ending letter

The example below shows using the % wildcard with the like operator to find the employees whom names are ending with the letter ‘n’.

The query with like operator:

Demo table with Like wildcard

Example of Like with % wildcard anywhere in a search term

Placing the % (wildcard) operator on the left and right of the given search term will search from the start and anywhere within the words to return the resultset.

The SQL query with like:

See this example online

Example of Like with % wildcard with number

This example returns all employees data whom salary is starting with digits 45 by using the SQL wildcard (%).

The query:

See this example online

Select – Like example with _ wildcard at starting letter

The example below shows using the _ (like wildcard) with the like operator to find the employees from tbl_employee table whom names are starting with letters ‘Mik’. It will only substitute one character.

The query with like is:

See this example online

As you can see, it only fetched one row.