TRIM()

Concepts covered: Whitespace Trimming

TRIM(text) removes whitespace from both ends of a value. LTRIM removes it from the left end only, RTRIM from the right end only, and none of the 3 touches spaces between words.

Edge whitespace is invisible in a result grid and decisive in a comparison. 'Low Tide ' and 'Low Tide' differ by 1 character, so they fail =, land in separate GROUP BY groups and refuse to pair in a JOIN. LENGTH shows what the grid hides, and wrapping the value in brackets with CONCAT shows where it starts and ends.

SELECT
  CONCAT('[', artist_name, ']') AS shown,
  LENGTH(artist_name) AS raw_length,
  LENGTH(TRIM(artist_name)) AS trimmed_length
FROM artist_signups

TRIM turns 3 spellings of Low Tide into the same 8 characters. Paper Moons keeps its double space at 12 characters, because the extra space sits between words. REPLACE(artist_name, ' ', ' ') shrinks each pair of spaces to 1 in a single pass, so a run of 3 spaces needs a second pass.

A join that drops rows with no error

A stream count file uploaded from a spreadsheet carries artist names as people typed them. Joined to the artist table on name, every row looks like a match, yet only 1 of them pairs.

Raw join

Compares the uploaded names exactly as stored.

SELECT
  s.artist_name,
  s.streams,
  a.artist_id
FROM stream_counts AS s
LEFT JOIN artists AS a
  ON s.artist_name = a.artist_name

Trimmed join

Trims the uploaded name inside the ON clause before comparing.

SELECT
  s.artist_name,
  s.streams,
  a.artist_id
FROM stream_counts AS s
LEFT JOIN artists AS a
  ON TRIM(s.artist_name) = a.artist_name

In the raw tab only Paper Moons gets an artist_id. The other 2 names read identically to the artist table's, but Low Tide ends in a space and Night Drive starts with one. Trimming inside the ON clause matches all 3. With an INNER JOIN the 2 unmatched artists would have dropped out of the total, leaving no NULL behind to hint that anything was missing.

Trim before you slice

A leading space pushes every position 1 to the right. SUBSTR(' en_US', 1, 2) returns a space followed by e, and STRPOS(' en_US', '_') returns 4 where the clean value gives 3. Trim first, then hand the clean value to SUBSTR, STRPOS or REPLACE.

Do

• Trim and lowercase both sides of a text join key: LOWER(TRIM(name))
• Check LENGTH beside the value when a match fails for no visible reason
• Trim once in a CTE and slice the trimmed column downstream

Avoid

• Trust a result grid to show trailing spaces
• Run SUBSTR or STRPOS on user-entered text before trimming it
• Expect TRIM to collapse spaces between words

city holds ' New York ', with 2 spaces at each end. What do LENGTH(RTRIM(city)) and LENGTH(TRIM(city)) return?

• 8 and 8
• 10 and 8
• 10 and 7
• 12 and 8

Capstone: the episodes that vanished

This case puts every function from the lesson into one broken report. Read the evidence, question the people involved and name the cause.

The fix in that case follows a fixed order. TRIM goes innermost, wrapped around the column at every place the column appears, because SUBSTR and STRPOS both read positions and each needs the cleaned value. For an email column with stray spaces, SUBSTR(TRIM(email), STRPOS(TRIM(email), '@') + 1) returns the domain whether or not the row was padded.

About this lesson

This section is part of the String Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.