For an instructor lead, in-depth look at learning SQL click below.
In SQL (Structured Query Language), we often need to find records in a table that match a specific pattern. This is an everyday task for any SQL programmer, and there’s built-in functionality in SQL to help us do this quickly and efficiently. This functionality is provided via the LIKE operator.
What is the LIKE Operator?
The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards we generally use in conjunction with the LIKE operator.
- %: The percent sign represents zero, one, or multiple characters.
- _: The underscore represents a single character.
SQL LIKE Syntax:
|
1 2 3 4 5 |
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; |
Example of LIKE Operator:
Scenario:
Let’s imagine we have a Customers table, and we want to find all customers whose name starts with ‘J’.
|
1 2 3 4 |
SELECT * FROM Customers WHERE CustomerName LIKE 'J%'; |
The above SQL statement selects all customers with a CustomerName starting with “J”. The % sign is used to define wildcards (missing letters) both before and after the pattern. Also, note that SQL is case insensitive.
Another Scenario:
If we want to select all customers with a CustomerName that have “er” in any position.
|
1 2 3 4 |
SELECT * FROM Customers WHERE CustomerName LIKE '%er%'; |
In conclusion:
Understanding how to use the SQL LIKE operator is a powerful tool to have in your SQL toolkit. However, remember that using the LIKE statement with wildcard characters can slow your query if you are retrieving data from large tables.