SQL Clean Extra Spaces

Combining TRIM (for the edges) with REGEXP_REPLACE (for the middle) — the complete fix for whitespace problems, covering both cases at once.

What & Why

TRIM alone fixes leading/trailing spaces. REGEXP_REPLACE alone fixes internal double-spacing. Combined, they fully normalize whitespace in a single expression — the complete answer to the gap the TRIM lesson flagged earlier in this series.

See How It Works

BUSINESS QUESTION

Product wants feedback comments normalized to single spaces.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

SELECT
  id AS feedback_id,
  TRIM(REGEXP_REPLACE(comment, '\s+', ' ', 'g')) AS cleaned_comment
FROM product.feedback
WHERE comment IS NOT NULL
ORDER BY feedback_id;

RESULT — exact output from the displayed Queryflo rows

feedback_id	cleaned_comment
1	Clear and useful
2	Export worked well
3	Helpful setup flow
4	Needs clearer timing

The fixture comments are already clean, so the pipeline preserves them.

Now You Try

Practice this concept

Product wants feedback comments normalized to single spaces.

Available schema

product

Prefix tables with product.table_name.

feedback_idcleaned_comment

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice