PostgreSQL LIKE Operator

What is LIKE?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It's essential for pattern matching and text searching in PostgreSQL.

Basic Syntax

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

Wildcard Characters

PostgreSQL LIKE supports two wildcard characters:

Wildcard	Description	Example
%	Matches zero or more characters	'a%' matches "a", "ab", "abc"
_	Matches exactly one character	'a_' matches "ab", "ac" but not "a" or "abc"

Basic LIKE Examples

Example 1: Starts With

-- Find customers whose name starts with 'John'
SELECT * FROM customers
WHERE customer_name LIKE 'John%';

-- Find products starting with 'Pro'
SELECT product_name, price
FROM products
WHERE product_name LIKE 'Pro%';

Example 2: Ends With

-- Find emails ending with '@gmail.com'
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- Find files ending with '.pdf'
SELECT filename FROM documents
WHERE filename LIKE '%.pdf';

Example 3: Contains

-- Find customers with 'son' anywhere in name
SELECT * FROM customers
WHERE customer_name LIKE '%son%';

-- Find products containing 'phone'
SELECT * FROM products
WHERE product_name LIKE '%phone%';

Example 4: Exact Length with Underscore

-- Find 3-letter codes
SELECT * FROM country_codes
WHERE code LIKE '___';

-- Find phone numbers in format XXX-XXXX
SELECT * FROM contacts
WHERE phone LIKE '___-____';

Combining Wildcards

-- Starts with 'A' and ends with 'n'
SELECT * FROM customers
WHERE customer_name LIKE 'A%n';

-- Second character is 'a'
SELECT * FROM products
WHERE product_name LIKE '_a%';

-- Contains 'book' and ends with 's'
SELECT * FROM products
WHERE product_name LIKE '%book%s';

Case Sensitivity

LIKE (Case-Sensitive)

-- Case-sensitive search
SELECT * FROM products
WHERE product_name LIKE 'iPhone%';  -- Matches 'iPhone 12' but not 'iphone 12'

ILIKE (Case-Insensitive)

-- Case-insensitive search
SELECT * FROM products
WHERE product_name ILIKE 'iphone%';  -- Matches 'iPhone 12', 'iphone 12', 'IPHONE 12'

-- Case-insensitive email search
SELECT * FROM users
WHERE email ILIKE '%@GMAIL.COM';  -- Matches any case variation

NOT LIKE

Finds rows that don't match the pattern:

-- Find customers not from USA
SELECT * FROM customers
WHERE country NOT LIKE 'USA';

-- Find non-PDF files
SELECT * FROM documents
WHERE filename NOT LIKE '%.pdf';

-- Exclude test emails
SELECT * FROM users
WHERE email NOT LIKE '%test%'
  AND email NOT LIKE '%demo%';

Escaping Special Characters

When you need to search for literal % or _ characters:

-- Search for literal underscore using ESCAPE
SELECT * FROM products
WHERE product_name LIKE '%\_off%' ESCAPE '\';

-- Search for literal percent sign
SELECT * FROM discounts
WHERE description LIKE '%50\%%' ESCAPE '\';

-- Alternative: Use different escape character
SELECT * FROM products
WHERE product_name LIKE '%#_off%' ESCAPE '#';

LIKE with Multiple Conditions

OR Conditions

-- Find customers from multiple cities
SELECT * FROM customers
WHERE city LIKE 'New%'
   OR city LIKE 'Los%'
   OR city LIKE 'San%';

AND Conditions

-- Find products that contain 'phone' and start with 'Smart'
SELECT * FROM products
WHERE product_name LIKE 'Smart%'
  AND product_name LIKE '%phone%';

Performance Considerations

1. Leading Wildcard Performance

-- Slow: Cannot use index
SELECT * FROM customers
WHERE customer_name LIKE '%son';

-- Fast: Can use index
SELECT * FROM customers
WHERE customer_name LIKE 'John%';

2. Use Indexes

-- Create index for LIKE queries
CREATE INDEX idx_customers_name ON customers(customer_name);

-- For case-insensitive searches, use expression index
CREATE INDEX idx_customers_name_lower ON customers(LOWER(customer_name));

-- Query using the index
SELECT * FROM customers
WHERE LOWER(customer_name) LIKE 'john%';

3. Full-Text Search Alternative

For complex text searches, consider full-text search:

-- Create full-text search index
CREATE INDEX idx_products_fts ON products 
USING gin(to_tsvector('english', product_name));

-- Full-text search (faster for complex patterns)
SELECT * FROM products
WHERE to_tsvector('english', product_name) @@ to_tsquery('english', 'phone');

Practical Examples

Example 1: Email Validation

-- Find valid email patterns
SELECT * FROM users
WHERE email LIKE '%_@__%.__%';

-- Find specific email domains
SELECT * FROM users
WHERE email ILIKE '%@gmail.com'
   OR email ILIKE '%@yahoo.com'
   OR email ILIKE '%@hotmail.com';

Example 2: Phone Number Patterns

-- Find US phone numbers (XXX-XXX-XXXX)
SELECT * FROM contacts
WHERE phone LIKE '___-___-____';

-- Find international numbers starting with +1
SELECT * FROM contacts
WHERE phone LIKE '+1%';

Example 3: Product Search

-- Search products by keyword
SELECT product_id, product_name, price
FROM products
WHERE product_name ILIKE '%laptop%'
   OR product_name ILIKE '%notebook%'
   OR description ILIKE '%laptop%'
ORDER BY price;

Example 4: File Management

-- Find image files
SELECT * FROM files
WHERE filename ILIKE '%.jpg'
   OR filename ILIKE '%.png'
   OR filename ILIKE '%.gif';

-- Find documents from specific year
SELECT * FROM documents
WHERE filename LIKE '%2024%';

LIKE with Other Clauses

With JOIN

-- Find orders from customers in specific cities
SELECT o.order_id, c.customer_name, c.city
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city LIKE 'New%';

With GROUP BY

-- Count customers by city prefix
SELECT 
    CASE 
        WHEN city LIKE 'New%' THEN 'New Cities'
        WHEN city LIKE 'San%' THEN 'San Cities'
        ELSE 'Other'
    END as city_group,
    COUNT(*) as customer_count
FROM customers
GROUP BY city_group;

With Subqueries

-- Find products in categories matching pattern
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE category_name LIKE '%Electronics%'
);

Advanced Patterns

Pattern 1: Multiple Word Search

-- Find products containing multiple keywords
SELECT * FROM products
WHERE product_name ILIKE '%wireless%'
  AND product_name ILIKE '%mouse%';

Pattern 2: Exclude Patterns

-- Find active products excluding test items
SELECT * FROM products
WHERE status = 'active'
  AND product_name NOT LIKE '%test%'
  AND product_name NOT LIKE '%sample%'
  AND product_name NOT LIKE '%demo%';

Pattern 3: Complex Email Filtering

-- Find corporate emails (exclude common free providers)
SELECT * FROM users
WHERE email LIKE '%@%.%'
  AND email NOT ILIKE '%@gmail.com'
  AND email NOT ILIKE '%@yahoo.com'
  AND email NOT ILIKE '%@hotmail.com'
  AND email NOT ILIKE '%@outlook.com';

SIMILAR TO (SQL Standard)

PostgreSQL also supports SIMILAR TO for regex-like patterns:

-- SIMILAR TO uses SQL standard regex
SELECT * FROM products
WHERE product_name SIMILAR TO '%(phone|tablet)%';

-- Equivalent to
SELECT * FROM products
WHERE product_name LIKE '%phone%'
   OR product_name LIKE '%tablet%';

Regular Expressions (More Powerful)

For complex patterns, use regex operators:

-- Using ~ (regex match, case-sensitive)
SELECT * FROM products
WHERE product_name ~ '^(iPhone|Samsung)';

-- Using ~* (regex match, case-insensitive)
SELECT * FROM products
WHERE product_name ~* '^(iphone|samsung)';

-- Using !~ (regex not match)
SELECT * FROM products
WHERE product_name !~ 'test|demo|sample';

Common Mistakes and Solutions

Mistake 1: Forgetting Wildcards

-- Wrong: Exact match only
SELECT * FROM customers
WHERE customer_name LIKE 'John';

-- Correct: Use wildcards
SELECT * FROM customers
WHERE customer_name LIKE '%John%';

Mistake 2: Case Sensitivity Issues

-- May miss results due to case
SELECT * FROM products
WHERE product_name LIKE 'iphone%';

-- Better: Use ILIKE
SELECT * FROM products
WHERE product_name ILIKE 'iphone%';

Mistake 3: Performance with Leading Wildcard

-- Slow: Cannot use index
SELECT * FROM customers
WHERE customer_name LIKE '%Smith';

-- Consider full-text search for better performance
SELECT * FROM customers
WHERE to_tsvector('english', customer_name) @@ to_tsquery('english', 'Smith');

Best Practices

1. Use ILIKE for Case-Insensitive: More readable than LOWER() with LIKE
2. Avoid Leading Wildcards: They prevent index usage
3. Create Appropriate Indexes: Use expression indexes for case-insensitive searches
4. Consider Full-Text Search: For complex text searching
5. Escape Special Characters: When searching for literal % or _
6. Use Specific Patterns: More specific patterns are faster
7. Test Performance: Profile queries with EXPLAIN ANALYZE

Summary

The LIKE operator is essential for pattern matching:

• Uses % (zero or more characters) and _ (exactly one character)
• LIKE is case-sensitive, ILIKE is case-insensitive
• NOT LIKE excludes matching patterns
• Leading wildcards prevent index usage
• Consider full-text search for complex patterns
• Use ESCAPE for literal wildcard characters
• Regular expressions provide more powerful pattern matching

Mastering LIKE is crucial for effective text searching and filtering in PostgreSQL.