TRIM / LOWER / UPPER
Normalize text for comparison, grouping, or display. TRIM strips leading and trailing whitespace (or a specified character set). LOWER / UPPER fold case. Used together they collapse near-duplicate strings that differ only in whitespace or case.
Syntax #
sql
SELECT
TRIM(col), -- strip outer whitespace
TRIM('x' FROM col), -- strip a specific char
LOWER(col),
UPPER(col)
FROM table_name;Example #
Loading SQL editor...
Info
Wrapping a column in a function breaks index usage. WHERE LOWER(email) = 'foo@bar.com' forces a full scan even with an index on email. Options: (1) store a normalized copy in a separate column and index that, (2) create a functional index on LOWER(email) (Postgres/DuckDB), or (3) compare case-insensitively at write-time so the query can stay as WHERE email = ?. The same caveat applies to TRIM and any other predicate that wraps the indexed column.