The LIKE operator in SQL is used to perform pattern matching in a WHERE clause. It allows you to find records based on specific patterns in string columns. Wildcards are special symbols that represent one or more characters in the pattern you are searching for. The two most commonly used wildcards with the LIKE operator are:
1. Percent sign (%): Represents zero or more characters.
2. Underscore (_) : Represents a single character.
Here’s how to use the LIKE operator with these wildcards:
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
Examples
1. Using the Percent Sign (%):
- To find all records where name starts with "J":
SELECT * FROM employees
WHERE name LIKE 'J%';
- To find all records where name ends with "son":
SELECT * FROM employees
WHERE name LIKE '%son';
- To find records where name contains "an":
SELECT * FROM employees
WHERE name LIKE '%an%';
2. Using the Underscore (_) :
- To find all records where name is four characters long and starts with "A":
SELECT * FROM employees
WHERE name LIKE 'A___';
- To find records where the second letter is "a":
SELECT * FROM employees
WHERE name LIKE '_a%';
3. Combining Both Wildcards:
- You can combine both wildcards in a single pattern:
SELECT * FROM employees
WHERE name LIKE 'A_%n%';
Important Notes
- The LIKE operator is case-sensitive in some databases (like PostgreSQL) and case-insensitive in others (like MySQL, by default). Check the behavior of your specific database.
- Be cautious when using % as it can lead to slower query performance, especially if it appears at the beginning of the pattern, as this prevents the database from using indexes effectively.
Using the LIKE operator along with wildcards provides powerful functionality for searching text data within SQL databases.