How does the trim and standardise text query work?

TRIM removes leading and trailing spaces, which are invisible in most tools but make 'London' and 'London ' different groups. INITCAP and UPPER settle the capitalisation. REGEXP_REPLACE with the 'g' flag removes every character that is not a digit or a plus sign from the phone number.

What commonly goes wrong with trim and standardise text in SQL?

Case-folding can merge values that should stay separate (for example product codes where case matters).

Trim and Standardise Text in SQL

Standardising text trims spaces, fixes case and strips stray characters so the same value is spelled one way before you group or join on it.

Example query

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

SELECT id,
       INITCAP(TRIM(city)) AS city_clean,
       UPPER(TRIM(country_code)) AS country_code_clean,
       REGEXP_REPLACE(TRIM(phone), '[^0-9+]', '', 'g') AS phone_digits
FROM customers;

How it works

TRIM removes leading and trailing spaces, which are invisible in most tools but make 'London' and 'London ' different groups.
INITCAP and UPPER settle the capitalisation.
REGEXP_REPLACE with the 'g' flag removes every character that is not a digit or a plus sign from the phone number.

MySQL, BigQuery, SQL Server and SQLite differences

INITCAP is PostgreSQL and Oracle; MySQL and SQL Server need a custom approach, and BigQuery has INITCAP. REGEXP_REPLACE exists in MySQL 8+, BigQuery (REGEXP_REPLACE(x, r'[^0-9+]', '')) and Oracle; older SQL Server versions have no built-in regular expression functions, so check yours.

Common mistakes

Case-folding can merge values that should stay separate (for example product codes where case matters).
Non-breaking spaces and other unusual whitespace are not removed by TRIM; replace them explicitly.
Cleaned phone numbers still need a country code before they can be matched across systems.

Ask for it in plain English

Instead of writing the query yourself, you can 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 query matches the definition your business uses.

Show distinct city names after trimming and fixing case.
How many customers have a phone number that is not just digits?