Blog

Introduction to SQL LIKE Operator

The SQL LIKE operator is a powerful tool used for pattern matching within strings. It allows you to search for a specified pattern within a column. This pattern can include wildcard characters to match any sequence of characters.

Syntax

The basic syntax of the LIKE operator is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

In the above syntax:

• column_name(s) refers to the column(s) you want to search.
• table_name is the name of the table containing the columns.
• pattern is the pattern you want to search for, which may include wildcard characters.

Wildcard Characters

Wildcard characters are special characters used in conjunction with the LIKE operator to represent unknown or variable characters.

1. % – Matches zero or more characters.
2. _ – Matches any single character.

Examples:

Let’s dive into some examples to understand how the LIKE operator works in different scenarios.

Basic Pattern Matching

Consider a table named employees with a column named name. We want to retrieve all employees whose names start with ‘J’.

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

Explanation:

• name LIKE 'J%' will match any name starting with ‘J’.
• % acts as a wildcard character here, matching zero or more characters.

Pattern Matching Any Single Character

Suppose we want to find employees whose names contain exactly five characters and start with ‘J’.

SELECT *
FROM employees
WHERE name LIKE 'J____';

Explanation:

• name LIKE 'J____' will match any name starting with ‘J’ and followed by four more characters.
• _ is used to match any single character.

Pattern Matching with Any Characters

Now, let’s find employees whose names contain ‘son’ anywhere in their names.

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

Explanation:

• name LIKE '%son%' will match any name containing ‘son’ anywhere in the string.
• % is used at the beginning and end to match any characters before and after ‘son’.

Case Insensitive Pattern Matching

If you want to perform a case-insensitive search, you can use the LOWER() or UPPER() functions along with the LIKE operator.

SELECT *
FROM employees
WHERE LOWER(name) LIKE '%john%';

Explanation:

• LOWER(name) converts the name column values to lowercase.
• LIKE '%john%' matches any name containing ‘john’ (case insensitive).

Conclusion

The LIKE operator in SQL is a versatile tool for pattern matching within strings. By using wildcard characters, you can create complex patterns to match specific criteria. Mastering the LIKE operator is essential for querying databases effectively and efficiently. Experiment with different patterns and scenarios to deepen your understanding.