How to use LIKE in SQL?

Welcome back to our SQL tutorial series! In this article, we’re going to explore the LIKE operator, which is used to search for a specified pattern in a column. The LIKE operator is particularly useful for filtering data based on partial matches, making it an essential tool for data querying and text searching.

Understanding the LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is often used with wildcard characters to match a portion of a string.

Wildcard Characters

• %: Represents zero, one, or multiple characters
• _: Represents a single character

Basic Syntax of LIKE

The basic syntax of the LIKE operator is as follows:

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

Let’s look at some practical examples to understand how to use the LIKE operator effectively.

Example Table: employees

We’ll continue to use the employees table for our examples:

Using LIKE with %

Example 1: Finding Names that Start with a Specific Letter

To find employees whose names start with ‘J’, you use:

SELECT name, position FROM employees WHERE name LIKE 'J%';

This command returns:

Example 2: Finding Names that End with a Specific Suffix

To find employees whose names end with ‘n’, you use:

SELECT name, position FROM employees WHERE name LIKE '%n';

This command returns:

Example 3: Finding Names that Contain a Specific Sequence

To find employees whose names contain ‘ar’, you use:

SELECT name, position FROM employees WHERE name LIKE '%ar%';

This command returns:

Using LIKE with _

Example 4: Finding Names with a Specific Pattern

To find employees whose names have ‘a’ as the second character, you use:

SELECT name, position FROM employees WHERE name LIKE '_a%';

This command returns:

Combining LIKE with Other Operators

You can combine the LIKE operator with other SQL clauses like AND, OR, and NOT to create more complex queries.

Example 5: Combining LIKE with AND

To find employees whose names start with ‘J’ and who are Data Analysts, you use:

SELECT name, position FROM employees WHERE name LIKE 'J%' AND position = 'Data Analyst';

This command returns:

Example 6: Combining LIKE with OR

To find employees whose names start with ‘J’ or end with ‘n’, you use:

SELECT name, position FROM employees WHERE name LIKE 'J%' OR name LIKE '%n';

This command returns:

Example 7: Combining LIKE with NOT

To find employees whose names do not contain ‘a’, you use:

SELECT name, position FROM employees WHERE name NOT LIKE '%a%';

This command returns:

Using LIKE with CASE Sensitivity

The case sensitivity of the LIKE operator depends on the database system. Some systems, like MySQL, are case-insensitive by default, while others, like PostgreSQL, are case-sensitive. You can use functions like LOWER or UPPER to ensure case-insensitive searches.

Example 8: Case-Insensitive Search in a Case-Sensitive System

To perform a case-insensitive search, you can convert the column and pattern to the same case:

SELECT name, position FROM employees WHERE LOWER(name) LIKE 'j%';

This command returns:

Conclusion

The LIKE operator is a powerful tool for searching and filtering data based on patterns in SQL. By understanding how to use wildcards and combining LIKE with other SQL clauses, you can perform complex text searches and extract valuable information from your data.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!