PostgreSQL ILIKE

The ILIKE operator in PostgreSQL is a case-insensitive version of the LIKE operator, used for pattern matching in SQL queries. It allows you to search for a specified pattern in a string, ignoring case distinctions. Note that ILIKE is specific to PostgreSQL and may not be available in other SQL environments.

Usage

The ILIKE operator is useful when you need to perform case-insensitive pattern matching in string comparison operations. It is often used in WHERE clauses to filter results based on a pattern regardless of case.

SELECT column1, column2, ...
FROM table_name
WHERE column_name ILIKE pattern;
제공

In this syntax, column_name ILIKE pattern specifies the column to search and the pattern to match, with the search being case-insensitive. Be aware that results can vary depending on character encodings and collations, especially in multilingual databases.

Examples

1. Basic Case-Insensitive Match

SELECT * 
FROM employees
WHERE first_name ILIKE 'john';
제공

This example retrieves all rows from the employees table where the first_name column matches 'john' in a case-insensitive manner, such as 'John', 'john', or 'JOHN'.

2. Using Wildcards

SELECT * 
FROM products
WHERE product_name ILIKE '%apple%';
제공

Here, the query searches for any occurrence of 'apple' within the product_name column, regardless of case, allowing for matches like 'Green Apple', 'apple', or 'APPLE pie'.

3. Complex Pattern Matching

SELECT * 
FROM orders
WHERE order_description ILIKE 'order #%_';
제공

This example uses the underscore (_) and percent sign (%) wildcards to match any single character and any sequence of characters, respectively, making it useful for more complex pattern matching while ignoring case.

Tips and Best Practices

• Use wisely for performance. ILIKE can be slower than LIKE due to its case-insensitive nature, so use it only when necessary.
• Combine with indexes. If possible, use functional indexes with LOWER() to enhance performance on large datasets when using ILIKE. For example:

  CREATE INDEX idx_lower_first_name ON employees (LOWER(first_name));제공

• Be mindful of locale settings. Performance can vary based on locale settings and character encodings.
• Optimize pattern usage. Use specific patterns to minimize the search scope and improve query speed. Avoid starting patterns with wildcards if possible.
• Consider using the citext module. For applications requiring frequent case-insensitive text comparison, the citext module provides a case-insensitive text type.
• Version considerations. While there are no major differences in ILIKE behavior across PostgreSQL versions, always check release notes for any minor updates or bug fixes.