The LIKE Operator

Definition: The LIKE operator is used in a WHERE clause to search for a specific pattern in a column. It is the primary tool for performing "partial matches" when you don't know the exact value you are looking for.

Why: Pattern matching is a fundamental skill in data retrieval. Whether you are searching for a user by the first few letters of their name or looking for all email addresses from a specific domain, LIKE provides the flexibility that standard comparison operators (=, <, >) cannot.

Syntax

The LIKE operator is used with special characters called wildcards to define the search pattern.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

Example: Starting with a Letter

To find all students whose names start with the letter "R", you use the % wildcard after the letter:

SELECT *
FROM students
WHERE name LIKE 'R%';

The Wildcard Characters

There are two main wildcards used with the LIKE operator:

Common Search Patterns

• 'a%': Finds any values that start with "a".
• '%a': Finds any values that end with "a".
• '%or%': Finds any values that have "or" in any position.
• '_r%': Finds any values that have "r" in the second position.
• 'a_%': Finds any values that start with "a" and are at least 2 characters in length.

Key Notes

• Case Sensitivity: In many databases (like MySQL), LIKE is case-insensitive. However, in others (like PostgreSQL), it is case-sensitive. Use ILIKE in PostgreSQL for a case-insensitive search.
• Performance: Using a wildcard at the beginning of a pattern (e.g., '%Ravi') can be slower on very large tables because the database cannot easily use its index to find the data.
• Quotes: Always enclose your patterns in single quotes, just like you would with any other text value.