SQL LIKE

What is the SQL LIKE Operator?

The LIKE operator in SQL is used to search for a specified pattern in a column. It is commonly used in WHERE clauses to filter rows based on matching patterns in text data.

Syntax of SQL LIKE

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

Key Features of the LIKE Operator

1. Pattern Matching: Allows flexible filtering based on partial matches.
2. Case Sensitivity: Behavior depends on the database. For example:
   • MySQL: Case-insensitive by default.
   • PostgreSQL: Case-sensitive by default.
3. Works with Wildcards: Supports % and _ as wildcards for flexible matching.

Wildcards in SQL LIKE

1. %: Represents zero, one, or multiple characters.

   • Example: '%abc' matches any text ending with "abc".

2. _: Represents exactly one character.

   • Example: 'a_c' matches "abc", "acc", but not "abcc".

Examples of SQL LIKE

1. Basic Pattern Matching

Retrieve customers whose names start with "Jo".

SELECT name
FROM customers
WHERE name LIKE 'Jo%';

2. Matching Patterns Anywhere

Retrieve products containing "phone" in their names.

SELECT product_name
FROM products
WHERE product_name LIKE '%phone%';

3. Matching a Specific Character

Retrieve employees whose names have "a" as the second character.

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

4. Matching Text Ending with a Pattern

Retrieve files with the ".txt" extension.

SELECT file_name
FROM files
WHERE file_name LIKE '%.txt';

Using NOT LIKE

The NOT LIKE operator filters rows that do not match the specified pattern.

Example

Retrieve employees whose names do not start with "A".

SELECT name
FROM employees
WHERE name NOT LIKE 'A%';

Case Sensitivity in LIKE

1. MySQL

By default, MySQL is case-insensitive for LIKE.

SELECT name
FROM customers
WHERE name LIKE 'jo%'; -- Matches "Joan", "JOHN", etc.

To enforce case sensitivity, use the BINARY keyword:

SELECT name
FROM customers
WHERE name LIKE BINARY 'jo%'; -- Matches "joan", but not "Joan"

2. PostgreSQL

PostgreSQL is case-sensitive for LIKE. Use ILIKE for case-insensitive searches:

SELECT name
FROM customers
WHERE name ILIKE 'jo%'; -- Matches "Joan", "john", etc.

Advanced Examples

1. Search for Patterns Containing Special Characters

To match % or _ as literal characters, escape them using the ESCAPE clause.

Retrieve rows where the name includes "100%":

SELECT name
FROM products
WHERE name LIKE '%100\%%' ESCAPE '\';

2. Combining LIKE with OR

Retrieve customers whose names start with "A" or "B".

SELECT name
FROM customers
WHERE name LIKE 'A%' OR name LIKE 'B%';

3. Using LIKE with Numbers (Implicit Conversion)

Retrieve orders where the ID starts with "10".

SELECT order_id
FROM orders
WHERE order_id LIKE '10%';

Performance Considerations

1. Indexes:

   • Leading wildcards (e.g., '%text') disable index usage, slowing performance.
   • Avoid starting patterns with % unless necessary.

2. Use Full-Text Search: For complex text searches in large datasets, consider using database-specific full-text search features.

3. Optimize Queries: Use LIKE selective patterns to minimize scanned rows.

Common Use Cases for LIKE

1. Searching for Substrings

Retrieve emails containing "gmail".

SELECT email
FROM users
WHERE email LIKE '%gmail%';

2. Filtering by File Extensions

Retrieve all .jpg files.

SELECT file_name
FROM files
WHERE file_name LIKE '%.jpg';

3. Filtering by Date Formats

Retrieve records for dates starting with "2025".

SELECT record_date
FROM records
WHERE record_date LIKE '2025%';

Best Practices for Using LIKE

1. Use Wildcards Judiciously: Avoid leading wildcards ('%text') to maintain query performance.
2. Escape Special Characters: Handle % and _ carefully when used as literals.
3. Consider Alternatives: For precise searches, use equality (=) or range queries instead of LIKE.

Conclusion

The SQL LIKE operator is a powerful tool for filtering data based on patterns. Its flexibility with wildcards allows for partial matches, making it ideal for text searches, filtering based on substrings, or pattern-based queries. However, for optimal performance, use LIKE thoughtfully and consider alternatives for large datasets or complex search requirements.