LIKE

• % represents any zero or more characters.
• _ represents any single character.

Performance

• abc%: Prefix search → PostgreSQL uses B-tree index → O(log n)
• %abc, %abc%: No fixed prefix → full table scan → O(n)
• a%b: Prefix search (Partial) → O(log n) then O(n)

ESCAPE

Treat the preserved characters % or _ like a normal character.

WHERE
  column1 LIKE pattern ESCAPE escape_character;

Example: Find the description contains the word 100%

# The query sets the '$' as escape character, placing before '%'
WHERE
  description LIKE '%100$%%' ESCAPE '$';

with CASE expression

SELECT
  product_name,
  CASE
    WHEN product_name LIKE '%iPhone%' THEN 'iPhone'
    WHEN product_name LIKE '%Galaxy%' THEN 'Galaxy'
  END AS group
FROM
  products
WHERE
  product_name LIKE '%iPhone%'
  OR product_name LIKE '%Galaxy%'
ORDER BY
  product_name;
  
  
       product_name        | group  
---------------------------+--------
 Apple iPhone 15           | iPhone 
 Apple iPhone 15 Pro Max   | iPhone 
 Samsung Galaxy Buds Pro 2 | Galaxy 
 Samsung Galaxy S24        | Galaxy 
 Samsung Galaxy Tab S9     | Galaxy 
 Samsung Galaxy Watch 6    | Galaxy 
 Samsung Galaxy Z Fold 5   | Galaxy 

ILIKE

Which is similar to LIKE but matches a pattern case-insensitively.

References

• https://www.pgtutorial.com/postgresql-tutorial/postgresql-like/
• https://www.coursera.org/specializations/postgresql-for-everybody
• https://chatgpt.com/