SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Changing case and trimming whitespace
Normalize messy text in SQL with UPPER, LOWER, TRIM, LTRIM and RTRIM, and know exactly which characters each one touches and which it leaves behind.
What you will learn
- Fold both sides of a comparison with lower(), not just the column
- Use TRIM, LTRIM and RTRIM for the ends; interior spaces are never affected
- Name the characters to strip: TRIM removes only spaces by default, not tabs or CR/LF
- Decide whether to normalize on write or in every read, and know the index cost
Understanding Changing case and trimming whitespace
UPPER and LOWER walk a string character by character and hand back a new string with each letter replaced by its cased counterpart; the stored row is untouched unless you write the result back. This matters because SQL equality is exact under the column's collation, so 'Ada@Example.COM' and 'ada@example.com' are simply two different values. Folding both sides of a comparison into the same case is what makes them comparable, which is why lower(col) = lower(input) is the working form and lower(col) = input is a bug waiting to happen.
TRIM works inward from the ends of the string and stops at the first character that is not in its trim set. The default set holds exactly one character, the space, so tabs, carriage returns, line feeds and non-breaking spaces all survive a bare trim(col), and interior runs of spaces are never candidates at all. LTRIM and RTRIM, or the standard TRIM(LEADING ... FROM ...) and TRIM(TRAILING ... FROM ...) spellings, restrict the work to one end, which is what you want for something like a fixed-width extract that right-pads every field.
Treat normalization as something that happens at a boundary rather than something sprinkled through queries. Either you clean on write, storing lower(trim(email)) and keeping the raw text in a separate column when you need an audit trail, or you clean on read and pay the CPU cost in every query while giving up the plain index on that column. Both operations are idempotent: trimming a trimmed string or lowering a lowered one changes nothing, so backfills are safe to re-run and a normalizing trigger cannot drift over time.
Because the functions return values rather than mutate them, you can always show the before and after side by side, which is the fastest way to prove that padding is really gone.
WITH signup(id, email) AS (
VALUES (1, ' Ada@Example.COM '),
(2, 'GRACE@example.com'),
(3, 'ada@example.com')
)
SELECT id,
length(email) AS raw_len,
lower(trim(email)) AS normalized,
length(lower(trim(email))) AS clean_len
FROM signup
ORDER BY id;UPPER, LOWER and TRIM never change stored data; they return a normalized copy, and TRIM only touches the ends of the string and only the characters you name, which is a single space if you name none.
Worked examples
Choosing the end and the characters
Shows that the optional TRIM argument is a set of characters and that only the ends of the value are considered.
SELECT trim(both '0' FROM '00420001234000') AS both_ends,
trim(leading '0' FROM '00420001234000') AS leading_only,
trim(trailing '0' FROM '00420001234000') AS trailing_only;Example explained
Line 1The second argument is a set of characters to strip, not a substring to delete, so '0' means "any zero at the edge".
Line 2Stripping halts at the first character outside that set, which is why the three zeros inside 420001234 are still there in every column.
Line 3LEADING and TRAILING confine the work to one end; BOTH is the default, so trim(x) and trim(both ' ' from x) are the same call.
Line 4PostgreSQL's ltrim(x, chars) and rtrim(x, chars) are the plain function-call spellings of the LEADING and TRAILING forms.
Whitespace that TRIM does not remove
Uses length() as a probe to show which invisible characters survive a default trim.
SELECT length(trim(E' Ada ')) AS spaces_only,
length(trim(E'\tAda\r\n')) AS tab_and_newline,
length(trim(E' \t Ada \r\n ')) AS mixed;Example explained
Line 1E'...' enables backslash escapes in PostgreSQL, so E'\t' is one tab character rather than a backslash followed by t.
Line 2The first column returns 3: both spaces go, the behaviour everyone expects from TRIM.
Line 3The second still returns 6 because a tab, a carriage return and a line feed are not the space character, so nothing is stripped at all.
Line 4The third stops at the tab on the left and the line feed on the right and returns 8; trim(both E' \t\r\n' from x) names the set explicitly and gets you 3.
Collapsing spellings into one group
Demonstrates why normalization has to happen inside GROUP BY, not after it.
WITH raw_tag(name) AS (
VALUES ('SQL'), (' sql '), ('Sql')
)
SELECT lower(trim(name)) AS tag, count(*) AS uses
FROM raw_tag
GROUP BY lower(trim(name));Example explained
Line 1The three raw values are three distinct strings, so GROUP BY name would return three rows with a count of 1 each.
Line 2lower(trim(name)) maps ' sql ', 'SQL' and 'Sql' onto the same text, so the grouping sees a single key with 3 rows.
Line 3The expression has to appear in GROUP BY (or be referenced by output name or ordinal) because grouping keys are computed values, not stored columns.
Important notes
The variants differ by dialect: Oracle's LTRIM and RTRIM take a character set, SQL Server's and MySQL's single-argument LTRIM and RTRIM remove spaces only, and SQLite offers trim(x, chars) but not the BOTH ... FROM spelling; SQLite's upper and lower also fold ASCII letters only, leaving accented characters unchanged.
Wrapping a column in lower() or trim() inside WHERE prevents a plain B-tree index on that column from being used, so index the expression or store the normalized form. Case folding is collation and locale dependent, so upper(lower(x)) is not guaranteed to give back x in every language.
Common mistakes
Expecting TRIM to fix internal spacing: trim('Ada Lovelace') is still 'Ada Lovelace', so it never equals 'Ada Lovelace'; collapsing interior runs needs a replace or a regex instead.
Normalizing one side only, as in WHERE lower(email) = 'Ada@Example.com': the left side can never contain capitals, so the query returns zero rows and looks like missing data rather than a bad predicate.
Treating TRIM as "remove all whitespace" after a CSV import: the last field of each line keeps a trailing carriage return, length(col) is one larger than the visible text, and joins or lookups on that column silently match nothing.
Try it yourself
Change, predict, then run
In a browser Postgres editor, write one SELECT over a three-row VALUES list of city names typed with random capitals, a leading tab and trailing spaces, returning the raw length, lower(trim(name)) and its length. Then swap in trim(both E' \t\r\n' from name) and note which lengths drop.
Open the SQL workspaceCheck your understanding
A country column loaded from a Windows CSV holds US followed by a carriage return. Why does WHERE trim(country) = 'US' still return no rows?
- BOTH is not the default, so trim(country) strips the left side only and the trailing bytes remain
- Comparisons are case-sensitive, so the predicate has to be lower(trim(country)) = 'us'
- TRIM strips the space character by default, and a carriage return is not a space, so the trimmed value is still one character longer than 'US'
- trim() returns a copy, so the WHERE clause is still evaluated against the untouched column value
Show answer
TRIM's default character set is the single space, so a carriage return is not a candidate and trim(country) hands back US with the CR still attached, which is not equal to 'US'. The BOTH option is wrong because BOTH is already the default in trim(country), and a one-sided trim would be irrelevant anyway since the stray character is at the end. Fix it by naming the set: trim(both E' \t\r\n' from country).