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.

Intermediate business practice