Pattern Matching in SQL

• LIKE clause is used to perform the pattern matching task in SQL.
• A WHERE clause is generally preceded by a LIKE clause in an SQL query.
• LIKE clause searches for a match between the patterns in a query with the pattern in the values present in an SQL table. If the match is successful, then that particular value will be retrieved from the SQL table.
• LIKE clause can work with strings and numbers.

The LIKE clause uses the following symbols known as wildcard operators in SQL to perform this pattern-matching task in SQL.

1. To represent zero, one or more than one character, % (percentage) is used.
2. To represent a single character _ (underscore) is used.

Let us start with the syntax of a LIKE clause:

Here, Expression refers to the pattern which we want to search for in the values of a table. This expression will include the wildcard operators such as '%' and '_'.

To understand this concept practically, let us see some examples.

Consider we have a table employee_details created into the database with the following data:

We will use the same table in all the examples. MySQL database and its syntax is used while writing the queries in the following examples.

(A) Using LIKE clause with % (percentage)

Example 1: Write a query to display employee details in which name starts with 'Pr'.

Query:

We have used the SELECT query with the WHERE clause applied on the Name column followed by the LIKE clause. We have specified the expression value as 'Pr' in the LIKE clause, followed by the wildcard operator percent (%). So, according to the query, all the records that have names starting with the string 'Pr' followed by any other character will be considered a part of the output.

You will get the following table as output:

There are two records in the employee_details, which has names starting with the string 'Pr'.

Example 2: Write a query to display employee details in which 'ya' is a substring in a name.

Query:

We have used the SELECT query with the WHERE clause applied on the Name column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as 'ya' preceded and followed by the wildcard operator percent(%). So, according to the query, all the records which have names containing 'ya' as the substring, followed and preceded by any other character, will be considered as a part of the output.

You will get the following table as output:

There are three records in the employee_details, which has names containing 'ya' as a substring.

Example 3: Write a query to display employee details in which city name ends with 'i'.

Query:

We have used the SELECT query with the WHERE clause applied on the City column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as 'i' preceded by the wildcard operator percent (%). So according to the query, all the records with city names ending with 'i' preceded by any other character will be considered a part of the output.

You will get the following table as output:

There are two records in the employee_details, which has city names ending with 'i'.

Example 4: Write a query to display employee details in which age number starts with 2.

Query:

We have used the SELECT query with the WHERE clause applied on the Age column followed by the LIKE clause. We have specified the expression value as '2' in the LIKE clause, preceded by the wildcard operator percent (%). So, according to the query, all the records that have age numbers starting with '2' followed by any other number will be considered as a part of the output.

You will get the following table as output:

There are three records in the employee_details, which has an age number starting with the digit '2'.

Example 5:

Write a query to display employee details in which salary contains a number 50 in between.

Query:

We have used the SELECT query with the WHERE clause applied on the salary column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as '50' preceded and followed by the wildcard operator percent (%). So, according to the query, all the records that have salary numbers as 50 in between and preceded and followed by any other number will be considered as a part of the output.

You will get the following table as output:

There is only one record in the employee_details, which has salary number '50' in between.

(B) Using LIKE clause with _ (underscore)

Example 1:

Write a query to display employee details in which city name starts with 'Na', ends with 'ik', and contains any single character between 'Na' and 'ik'.

Query:

We have used the SELECT query with the WHERE clause applied on the City column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as 'Na' followed by the wildcard operator underscore (_) with the string 'ik'. So, according to the query, all the records that have city names starting with 'Na' followed by any single character and ending with 'ik' will be considered as a part of the output.

You will get the following table as output:

There are two records in the employee_details, which has city names starting with 'Na' and ending with 'ik'.

Example 2:

Write a query to display employee details in which salary contains a number starting with '3' succeeding any two digits and finally ends with '00'.

Query:

We have used the SELECT query with the WHERE clause applied on the Salary column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as '3' followed by the wildcard operator underscore (_) twice with the numbers '00'. So, according to the query, all the records that have a salary starting with 3 followed by any number and ending with '00' will be considered as a part of the output.

You will get the following table as output:

There are two records in the employee_details, which has salary starting with the digit 3 and ending with double zero (00).

(C) Using LIKE clause with % and _ operator in a single query

Example 1: Write a query to display employee details in which employee name contains 'a' at fifth position.

Query:

We have used the SELECT query with the WHERE clause applied on the Name column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as the wildcard operator underscore (_) five times, followed by 'a' with another wildcard operator percentage (%). So according to the query, all the records which has names starting with any five alphabets followed by an alphabet 'a' and ending with any other alphabet will be considered as a part of the output.

You will get the following table as output:

There are three records in the employee_details, which has a name starting with any five alphabet followed by 'a' and ending with any alphabet.

Example 2:

Write a query to display employee details in which salary contains a substring '00' starting from the 5^th position.

Query:

We have used the SELECT query with the WHERE clause applied on the Salary column followed by the LIKE clause. In the LIKE clause, we have specified the expression value as the wildcard operator underscore (_) twice, followed by double zero ending with another wildcard operator percentage (%). So, according to the query, all the records that have salary starting with any two digits followed by a double zero and ending with any number will be considered as a part of the output.

You will get the following table as output:

There are four records in the employee_details, which has salary starting with any two digits followed by double zero and ending with any number.

(D) Using LIKE clause with NOT operator

Example 1:

Write a query to display employee details in which employee name is not like 'Priya'.

Query:

We have used the SELECT query with the WHERE clause applied on the Name column followed by the LIKE clause preceded by NOT. In the LIKE clause, we have specified the expression value as 'Priya' followed by the wildcard operator percentage (%). So, according to the query, all the records that have names not starting with 'Priya' followed by any alphabet will be considered as a part of the output.

You will get the following table as output:

There are three records in the employee_details, which has names not starting with 'Priya' followed by any alphabets.

Example 2:

Write a query to display employee details in which employee name is not like any single character followed by 'Na' and ending with 'ik'.

Query:

We have used the SELECT query with the WHERE clause applied on the City column followed by the LIKE clause preceded by NOT. In the LIKE clause, we have specified the expression value as 'Na' followed by the wildcard operator underscore (_) with 'ik'. So, according to the query, all the records that have names not starting with 'Priya' followed by any alphabet will be considered as a part of the output.

You will get the following table as output:

There are three records in the employee_details, which has employee names not starting with 'Na' followed by any single alphabet and ending with 'ik'.