Kenhuang Academy
  • English

      • 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:

      WildcardDescriptionExample
      %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%';
      -- 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

      -- 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.