LIKE % _

-- Wildcards:
-- % = any sequence of characters (including empty)
-- _ = exactly one character

-- Starts with
WHERE email LIKE 'priya%'        -- emails starting with 'priya'

-- Ends with
WHERE email LIKE '%@gmail.com'   -- all Gmail addresses

-- Contains
WHERE name LIKE '%kumar%'        -- names containing 'kumar'
WHERE name LIKE '%Kumar%'        -- case-sensitive! (most DBs)

-- Exact pattern with _
WHERE phone LIKE '+91__________'  -- Indian mobile format (10 digits after +91)
WHERE product_code LIKE 'PROD-___'  -- PROD- followed by exactly 3 chars

-- Case-insensitive (PostgreSQL)
WHERE LOWER(name) LIKE '%kumar%'
WHERE name ILIKE '%kumar%'        -- PostgreSQL: case-insensitive LIKE

-- NOT LIKE
WHERE email NOT LIKE '%@test.com'
WHERE email NOT LIKE '%+%'        -- exclude emails with + in them

-- Search products by name (partial match)
SELECT
  id, name, price, category_id
FROM products
WHERE
  LOWER(name) LIKE LOWER('%' || :search_term || '%')
  AND is_active = TRUE
ORDER BY
  CASE
    WHEN LOWER(name) LIKE LOWER(:search_term || '%') THEN 1  -- starts with = top result
    WHEN LOWER(name) LIKE LOWER('%' || :search_term) THEN 2  -- ends with
    ELSE 3                                                     -- contains
  END,
  name
LIMIT 20;

-- Find orders with specific patterns
SELECT * FROM orders
WHERE order_number LIKE 'ORD-2025-%'   -- all 2025 orders
  AND order_number NOT LIKE '%-TEST-%'; -- exclude test orders