The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Two wildcards are often used in conjunction with the LIKE operator:

The percent sign % represents zero, one, or multiple characters.
The underscore sign _ represents one, single character. function returns the average value of a numeric column.

Example

Select all employee that starts with the letter "p":
 SELECT *
 FROM  employee 
              
 WHERE  Employee_name  LIKE   'p%' ;

You can click on above box to edit the code and run again.

Output

Syntax

 SELECT  column1,column2,...
 FROM  table_name
              
 WHERE  columnN LIKE pattern;

Demo Employee table

This employee table is used for examples:

The _ Wildcard

The _ wildcard represents a single character.

It can be any character or number, but each _ represents one, and only one, character.

Example

Return all employee from a country that starts with 'I' followed by one wildcard character, then 'di' and then one wildcard characters:
SELECT*
FROM  employee
WHERE Country LIKE  'I_di_';

You can click on above box to edit the code and run again.

Output

The % Wildcard

The % wildcard represents any number of characters, even zero characters.

Example

Return all employee from a country that contains the letter 'I':

SELECT*
FROM  employee
WHERE Country LIKE  '%i%';

You can click on above box to edit the code and run again.

Output

Starts With

To return records that starts with a specific letter or phrase, add The % at the end of the letter or phrase.

Example

Return all employee that starts with 'ja':

SELECT*
FROM  employee
WHERE Employee_name LIKE  'ja%';

You can click on above box to edit the code and run again.

Output

Tip: You can also combine any number of conditions using AND or OR operators.

Example

Return all employee that starts with 'R' or starts with 'A':

SELECT*
FROM  employee
WHERE Employee_name LIKE  'R%'
 OR  Employee_name LIKE  'A%';

You can click on above box to edit the code and run again.

Output

Ends With

To return records that ends with a specific letter or phrase, add The % at the beginning of the letter or phrase.

Example

Return all employee that ends with 'A':

SELECT*
FROM  employee
WHERE Employee_name LIKE  '%A';

You can click on above box to edit the code and run again.

Output

Tip: You can also combine "starts with" and "ends with":

Example

Return all employee that starts with 'P' and ends with 'A':

SELECT*
FROM  employee
WHERE Employee_name LIKE  'P%A'
;

You can click on above box to edit the code and run again.

Output

Contains

To return records that contains a specific letter or phrase, add the '%' both before and after the letter sh phrase.

Example

 Return all employee that contains the phrase 'sh'

SELECT*
FROM  employee
WHERE Employee_name LIKE  '%sh%';

You can click on above box to edit the code and run again.

Output

Combine Wildcards

Any wildcard, like% and _ , can be used in combination with other wildcards.

Example

Return all employee that starts with "a" and are at least 3 characters in length:

SELECT*
FROM  employee
WHERE Employee_name LIKE  'a__%';

You can click on above box to edit the code and run again.

Output

Example

Return all employee that have "i" in the second position:

SELECT*
FROM  employee
WHERE Employee_name LIKE  '_i%'
  ;

You can click on above box to edit the code and run again.

Output

Without Wildcard

If no wildcard is specified, the phrase has to have an exact match to return a result.

Example

Return all employee from Germany:

SELECT*
FROM  employee
WHERE Country LIKE  'Germany';

You can click on above box to edit the code and run again.

Output

Wildcard Characters

wildcard characters