How does the SQL version of TRIM work?

TRIM(name) removes leading and trailing spaces. REGEXP_REPLACE with ' +' replaces runs of spaces with a single space, which is the rest of what Excel's TRIM does. Run this before grouping, joining or counting distinct values so that 'London ' and 'London' are the same.

Why can SQL give a different result from TRIM?

Excel's TRIM also collapses repeated internal spaces; SQL's TRIM does not, hence the regular expression.

TRIM in SQL

TRIM removes extra spaces from text; in SQL TRIM removes spaces at the ends, and a regular expression collapses repeated spaces inside.

The Excel version

=TRIM(A2)

The SQL equivalent

This example assumes: customers(id bigint, name text, city text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       TRIM(name) AS name_trimmed,
       REGEXP_REPLACE(TRIM(city), ' +', ' ', 'g') AS city_clean
FROM customers
ORDER BY id;

How it works

TRIM(name) removes leading and trailing spaces.
REGEXP_REPLACE with ' +' replaces runs of spaces with a single space, which is the rest of what Excel's TRIM does.
Run this before grouping, joining or counting distinct values so that 'London ' and 'London' are the same.

Where the results differ from Excel

Excel's TRIM also collapses repeated internal spaces; SQL's TRIM does not, hence the regular expression.
Neither removes non-breaking spaces (character 160); replace them explicitly.
TRIM only changes the query result; use an UPDATE (with care) if you want to fix the stored data.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Show distinct city names after trimming spaces.
How many customers have extra spaces in their name?