DbSchema Tutorial | SQL LIKE Operator

Publish on DbSchema Blog >>>

SQL, or Structured Query Language, is the standard language for communicating with relational databases. It is used for managing and querying data stored in databases. One of the powerful features of SQL is its ability to filter data using various operators. One such operator is the LIKE operator, which is used for pattern matching in SQL queries.

In this tutorial, we will delve deep into the LIKE operator in SQL, understanding its use cases, syntax, and various wildcard characters associated with it. We will also go through examples using a sample database table and explain the query results.

SQL LIKE Operator

The LIKE operator in SQL is used to filter data based on pattern matching. It is mainly used with the WHERE clause to search for a specific pattern in a column.

Syntax

The syntax for using the LIKE operator is as follows:

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

Example

Let’s consider a sample database table named Employees:

EmployeeID	Name	Department
1	John Doe	Sales
2	Jane Doe	Marketing
3	Sam Smith	IT
4	Mike Johnson	Sales
5	Emily Davis	Marketing

Now, let’s run a query to find all employees whose names start with J:

SELECT *
FROM Employees
WHERE Name LIKE 'J%';

Result

EmployeeID	Name	Department
1	John Doe	Sales
2	Jane Doe	Marketing

Explanation

The query will return all employees whose names start with J. The % wildcard is used to represent zero or more characters.

Wildcard Characters

The LIKE operator uses wildcard characters to match patterns. The following table lists the common wildcard characters used with the LIKE operator:

Wildcard	Description
%	Represents zero or more characters.
_	Represents a single character.
[]	Represents any single character within the brackets.
[^]	Represents any single character not in the brackets.

Using % Wildcard

The % wildcard character is used to represent zero, one, or multiple characters in a pattern.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern%';

Example

Using the same Employees table, let’s find all employees whose department ends with s:

SELECT *
FROM Employees
WHERE Department LIKE '%s';

Result

EmployeeID	Name	Department
1	John Doe	Sales
4	Mike Johnson	Sales

Explanation

The query returns all employees whose department ends with s. The % wildcard represents any characters before s.

Using _ Wildcard

The _ wildcard character is used to represent a single character in a pattern.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern_';

Example

Let’s find all employees whose Name has a as the second character:

SELECT *
FROM Employees
WHERE Name LIKE '_a%';

Result

EmployeeID	Name	Department
2	Jane Doe	Marketing
5	Emily Davis	Marketing

Explanation

In this case, the query returns all employees whose names have a as the second character.

Using [] Wildcard

The [] wildcard characters are used to represent any single character within the brackets.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE '[list_of_characters]%';

Example

Let’s find all employees whose Name starts with J or S:

SELECT *
FROM Employees
WHERE Name LIKE '[JS]%';

Result

EmployeeID	Name	Department
1	John Doe	Sales
2	Jane Doe	Marketing
3	Sam Smith	IT

Explanation

The query returns all employees whose names start with either J or S.

Using ^ Wildcard

The ^ wildcard character, when used inside [], represents any single character not in the brackets.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE '[^list_of_characters]%';

Example

Let’s find all employees whose Name does not start with J or S:

SELECT *
FROM Employees
WHERE Name LIKE '[^JS]%';

Result

EmployeeID	Name	Department
4	Mike Johnson	Sales
5	Emily Davis	Marketing

Explanation

The query returns all employees whose names do not start with J or S.

Using LIKE Operator with OR Operator

The LIKE operator can be combined with the OR operator to filter data based on multiple patterns.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern1'
OR column_name LIKE 'pattern2';

Example

Let’s find all employees whose Name starts with J or ends with s:

SELECT *
FROM Employees
WHERE Name LIKE 'J%'
OR Name LIKE '%s';

Result

EmployeeID	Name	Department
1	John Doe	Sales
2	Jane Doe	Marketing
3	Sam Smith	IT

Explanation

The query returns all employees whose names start with J or end with s.

Using NOT Operator with the LIKE Condition

The NOT operator can be used with the LIKE operator to filter data that does not match a specific pattern.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT LIKE 'pattern%';

Example

Let’s find all employees whose Name does not start with J:

SELECT *
FROM Employees
WHERE Name NOT LIKE 'J%';

Result

EmployeeID	Name	Department
3	Sam Smith	IT
4	Mike Johnson	Sales
5	Emily Davis	Marketing

Explanation

The query returns all employees whose names do not start with ‘J’.

Using Escape Characters with LIKE Operator

Sometimes, we need to search for patterns that include the wildcard characters (%, _, etc.). In such cases, we can use an escape character to treat the wildcard as a regular character.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern!%' ESCAPE '!';

Example

Let’s say we have an Orders table:

OrderID	ProductName
1	10% off
2	20% off
3	30% discount

Now, let’s find all orders with a 10% discount:

SELECT *
FROM Orders
WHERE ProductName LIKE '10!%%' ESCAPE '!';

Result

OrderID	ProductName
1	10% off

Explanation

The query returns all orders with a 10% discount. The ! character is used as an escape character to treat the % as a regular character.

Using NOT LIKE with [] Wildcard

We can also use the NOT operator with the LIKE operator and [] wildcard.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT LIKE '[list_of_characters]%';

Example

Using the Employees table, let’s find all employees whose Name does not start with E or M:

SELECT *
FROM Employees
WHERE Name NOT LIKE '[EM]%';

Result

EmployeeID	Name	Department
1	John Doe	Sales
2	Jane Doe	Marketing
3	Sam Smith	IT

Explanation

The query returns all employees whose names do not start with E or M.

Common Mistakes

1. Not Using the Correct Wildcard Character: It is common to mix up the % and _ wildcard characters. Remember, % represents zero or more characters, while _ represents a single character.

2. Overlooking Case Sensitivity: SQL is case-sensitive. So, LIKE 'j%' and LIKE 'J%' are different and will yield different results.

3. Forgetting to Use the ESCAPE Character: When searching for patterns that include wildcard characters, it is necessary to use an escape character.

FAQs

1. Is the LIKE operator case-sensitive?

   • Yes, the LIKE operator is case-sensitive. LIKE 'a%' will match all values starting with ‘a’ but will not match values starting with ‘A’. To perform a case-insensitive search, you can convert the column to lowercase or uppercase using the LOWER() or UPPER() function, respectively.

2. Can I use multiple wildcard characters in a single pattern?

   • Yes, you can use multiple wildcard characters in a single pattern. For example, LIKE 'J%n' will match all values starting with ‘J’ and ending with ‘n’.

3. Can I use the LIKE operator with numeric values?

   • Yes, the LIKE operator can be used with numeric values. However, it is more commonly used with text data.

Practice Questions

1. Write a SQL query to find all employees whose Name starts with J and ends with e.
2. Write a SQL query to find all employees whose Department does not start with S or M.
3. Write a SQL query to find all employees whose EmployeeID is a three-digit number.
4. Write a SQL query to find all orders whose ProductName contains the string off.

Conclusion

In this tutorial, we covered the LIKE operator in SQL and its various applications. We discussed the different wildcard characters and how they can be used with the LIKE operator. We also looked at examples using a sample database table and explained the results of each query.

Remember to use the LIKE operator carefully, as incorrect usage of wildcard characters can lead to unexpected results. Practice the queries mentioned in this tutorial and try the practice questions to strengthen your understanding of the LIKE operator.

Happy Querying!