How ILIKE works in SQL? Best ILIKE examples

How ILIKE works in SQL? Best ILIKE examples

When working with SQL, searching for text patterns is essential. The typical LIKE operator is useful but case-sensitive in many databases. Fortunately, some SQL databases, like PostgreSQL, offer a better alternative: ILIKE. In this article, I’ll break down how ILIKE works, when to use it, and some of the best examples to improve your SQL queries.

What is ILIKE in SQL?

The ILIKE operator is a case-insensitive version of LIKE. It’s particularly useful when filtering text data without worrying about case differences. Unlike LIKE, which distinguishes between uppercase and lowercase, ILIKE treats them as equivalent.

Databases That Support ILIKE

Not all SQL databases support ILIKE. It is commonly found in:

  • PostgreSQL
  • Some other databases with custom implementations

For MySQL and SQL Server, you’ll have to use functions like LOWER() or UPPER() to achieve a similar effect.

Basic ILIKE Syntax

Using ILIKE is straightforward. The syntax is almost identical to LIKE:


SELECT column_name
FROM table_name
WHERE column_name ILIKE 'pattern';

The pattern supports:

  • % – Matches any sequence of characters (wildcard).
  • _ – Matches a single character.

Best ILIKE Examples

Here are some practical scenarios demonstrating how ILIKE can simplify text searching.

1. Searching for a Word Regardless of Case


SELECT * FROM employees
WHERE name ILIKE 'john';

This query retrieves records where the name is “John”, “john”, “JOHN”, or any other casing variation.

2. Using Wildcards for Partial Matching


SELECT * FROM products
WHERE description ILIKE '%smartphone%';

This will return any product with “smartphone” as part of its description, no matter its capitalization.

3. Finding Words That Start with a Certain Letter


SELECT * FROM cities
WHERE name ILIKE 'n%';

This retrieves all cities where the name starts with “N” or “n”.

4. Using an Underscore Placeholder


SELECT * FROM users
WHERE username ILIKE 'J_n%';

This retrieves usernames such as “Jon”, “Jan”, or “Jen”, followed by any number of characters.

Performance Considerations

While ILIKE is powerful, it comes with some performance trade-offs. Here’s what you need to consider:

  • Indexing: Standard indexes may not speed up ILIKE searches effectively. Consider using a GIST or GIN index with a text-based extension like pg_trgm in PostgreSQL.
  • Large Datasets: Searching with % at the beginning can slow down queries significantly since it prevents index usage.

Alternative Approaches

If your database does not support ILIKE, use:


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

This achieves the same result but may be slower on large datasets due to function usage.

When to Use ILIKE vs. LIKE?

Operator Case Sensitivity Best Use Case
LIKE Case-sensitive When exact case matters
ILIKE Case-insensitive When searching without case distinction

Conclusion

ILIKE is a powerful tool for case-insensitive text searches in SQL. If you’re using PostgreSQL, it simplifies queries significantly, eliminating the need for extra functions. However, always consider performance implications and indexing strategies to optimize your searches.

 

Other interesting article:

How LIKE works in SQL? Best LIKE examples