PostgreSQL LIKE Operator

The LIKE operator in PostgreSQL is used to search for a specified pattern in a column. It is commonly used in WHERE clauses for pattern matching with text data. PostgreSQL also supports case-insensitive pattern matching using ILIKE.

1. Basic Syntax

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

- pattern can include wildcards:

• % → matches zero or more characters
• _ → matches exactly one character

2. Example: Basic Pattern Matching

-- Find employees whose name starts with 'A'
            SELECT name, department
            FROM employees
            WHERE name LIKE 'A%';

            -- Find employees whose name ends with 'son'
            SELECT name, department
            FROM employees
            WHERE name LIKE '%son';

            -- Find employees whose name contains 'ar'
            SELECT name, department
            FROM employees
            WHERE name LIKE '%ar%';

3. Using Single Character Wildcard (_)

-- Find 5-letter names starting with 'J'
            SELECT name
            FROM employees
            WHERE name LIKE 'J____';

Here, _ represents a single character, so J____ matches names like "James" or "Julia".

4. Case-Insensitive Search with ILIKE

-- Find employees whose name contains 'ar', case-insensitive
            SELECT name, department
            FROM employees
            WHERE name ILIKE '%ar%';

The ILIKE operator ignores case, unlike LIKE which is case-sensitive.

5. Escaping Special Characters

If your search string contains a wildcard character (% or _), you can escape it using a backslash (\).

-- Find names containing a literal underscore
            SELECT name
            FROM employees
            WHERE name LIKE '%_%' ESCAPE '\';

6. Combining LIKE with AND, OR, and NOT

-- Names starting with 'A' and ending with 'n'
            SELECT name
            FROM employees
            WHERE name LIKE 'A%' AND name LIKE '%n';

            -- Names not containing 'ar'
            SELECT name
            FROM employees
            WHERE name NOT LIKE '%ar%';

7. LIKE with JOINs and Aggregates

-- Count employees whose name contains 'ar' in each department
            SELECT department, COUNT(*) AS total
            FROM employees
            WHERE name LIKE '%ar%'
            GROUP BY department
            ORDER BY total DESC;

8. Best Practices

• Use ILIKE for case-insensitive searches.
• Place % at the end of the string for better performance when possible.
• Avoid leading % in large tables, as it prevents index usage.
• Combine with other conditions to narrow down search results.

Conclusion

The LIKE operator is a powerful tool in PostgreSQL for pattern-based text searching. It is widely used in data filtering, reporting, and search functionalities. Mastery of LIKE and ILIKE allows you to perform flexible and efficient text queries. In the next tutorial, we will explore the IN operator for matching multiple values efficiently.