Case-insensitive search (ILIKE / LOWER / collation)

“Find ‘acme’ whether it’s stored as ACME or Acme.” The portable approach is to lower-case both sides:

SELECT * FROM companies
WHERE LOWER(name) = LOWER(:q);          -- exact, case-insensitive

SELECT * FROM companies
WHERE LOWER(name) LIKE LOWER(:q) || '%'; -- prefix search

Dialect shortcuts:

• PostgreSQL / DuckDB: ILIKE does a case-insensitive LIKE: WHERE name ILIKE :q.
• MySQL: the default collation is usually case-insensitive (..._ci), so plain = and LIKE already ignore case. Force it with WHERE name LIKE :q COLLATE utf8mb4_general_ci if needed.
• SQLite: LIKE is case-insensitive for ASCII by default; = is case-sensitive. Use LOWER() for reliable Unicode behavior.
• Snowflake: string comparison is case-sensitive by default — use ILIKE or LOWER().
• BigQuery: case-sensitive — use LOWER(), or REGEXP_CONTAINS(name, r'(?i)acme').

Keep it index-friendly. WHERE LOWER(name) = LOWER(:q) can’t use a plain index on name. Add a functional index on the expression (Postgres/SQLite: CREATE INDEX ON companies (LOWER(name));), or store a normalized name_lower column and index that. For accent-insensitive matching too, use a case- and accent-insensitive collation (e.g. Postgres citext/ICU collations, und-ci-ai).