SQL Pattern Matching

SQL allows search for patterns in data when you don't know the exact word or phrase you are looking for.

It can be done using the LIKE keyword with wildcard characters.

Wildcard character	Usage
%	To specify the existence of 0, 1, or more characters
_ (underscore)	To specify the existence of a single character

Examples

Here is a list of some of the common and most frequently used SQL Queries using pattern matching.

Select employees, whose names start with the letter 'A'.

SELECT * FROM employees WHERE name LIKE 'A%';

Select employees, whose names contain the word 'ABC' anywhere.

SELECT * FROM employees WHERE name LIKE '%ABC%';

Select employees, whose names contain the letter 'A' in the second position.

SELECT * FROM employees WHERE name LIKE '_A%';

Select employees, whose names start with 'A' and are at least 3 characters in length.

SELECT * FROM employees WHERE name LIKE 'A__%';

Select employees, whose names start with the letter 'A' and end with the letter 'B'.

SELECT * FROM employees WHERE name LIKE 'A%B';

Select employees, whose names start with the letter from a range of characters C to H.

SELECT * FROM employees WHERE name LIKE '[C-H]%';

Select employees, whose names contain exactly five characters or five characters in length.

SELECT * FROM employees WHERE name LIKE '_____';

Select employees, whose names start with 'ABC' or 'XYZ'.

SELECT * FROM employees WHERE name LIKE 'ABC%' OR name LIKE 'XYZ%';

Select employees, whose names start with 'AB' or start with 'EF' or end with 'PQ' or end with 'XY' and are of 5 characters in length.

SELECT * FROM employees WHERE name LIKE 'AB%' OR name LIKE 'EF%' OR name LIKE '%PQ' or name LIKE '%XY' AND name LIKE '_____';

Select employees, whose names do not start with the letter 'A'.

SELECT * FROM employees WHERE name NOT LIKE 'A%';

and so on.

Overall

We now know how to write SQL queries with pattern matching.

Related Links

• SQL Tutorial
• SQL Examples
• SQL Questions