REPLACE()

Concepts covered: String Replacement

REPLACE(text, search, replacement) finds every occurrence of search and puts replacement in its place. With only 2 arguments it deletes each match. Where SUBSTR cuts by position, REPLACE works by content: you name the characters, and it finds them wherever they sit.

Numbers stored as display text

Finance exports format money for people to read. $12,400.75 won't CAST to DECIMAL, because $ and , are not digits, and TRY_CAST quietly turns it into NULL. Delete those 2 symbols and the text that remains is one the cast accepts.

SELECT
  artist_name,
  amount_text,
  CAST(REPLACE(REPLACE(amount_text, ','), '$') AS DECIMAL(10, 2)) AS amount
FROM royalty_exports

Read nested calls from the inside out. The inner REPLACE deletes every comma, the outer one deletes the dollar sign from that result, and CAST receives 12400.75. Each call takes the string the previous call produced, so a chain of REPLACE calls is a small pipeline whose first step is the innermost.

The order of swaps changes the answer

Because each step sees the output of the step before it, 2 swaps that touch the same character can collide. Many European exports write 1.250,00 for the amount 1250: dots group the thousands and the comma marks the decimals. Converting it means deleting the dots and turning the comma into a dot, and only 1 order of those 2 steps works.

Dots first: 1250.00

• REPLACE('1.250,00', '.') gives 1250,00
• REPLACE('1250,00', ',', '.') gives 1250.00
• CAST to DECIMAL returns 1250.00

Comma first: 125000

• REPLACE('1.250,00', ',', '.') gives 1.250.00
• REPLACE('1.250.00', '.') deletes both dots: 125000
• CAST to DECIMAL returns 125000.00

The wrong order raises no error. It produces a number that casts cleanly and is 100 times too large, the kind of bug that survives until someone reconciles the revenue table against the bank.

Every match, in every context

REPLACE can't tell a match you meant from one you did not. REPLACE('carla.combs@mail.com', '.com') deletes both occurrences of .com and returns carlabs@mail. It's case-sensitive as well: REPLACE('Hip-Hop', 'hip-hop', 'hip hop') leaves the value unchanged, because H and h are different characters.

Lowercase first and then replace, and hand-typed tags stop splitting into look-alike groups. The 2 tabs below count plays per genre tag, once as typed and once normalized.

As typed

Groups the tags exactly as listeners typed them, so each spelling counts on its own.

SELECT
  genre_tag,
  COUNT(*) AS plays
FROM genre_plays
GROUP BY genre_tag
ORDER BY plays DESC

Normalized

Lowercases each tag and turns hyphens into spaces before grouping.

SELECT
  REPLACE(LOWER(genre_tag), '-', ' ') AS genre,
  COUNT(*) AS plays
FROM genre_plays
GROUP BY REPLACE(LOWER(genre_tag), '-', ' ')
ORDER BY plays DESC

The first tab returns 6 groups for what listeners hear as 2 genres. The normalized tab folds them into hip hop with 5 plays and indie pop with 2. GROUP BY repeats the whole expression because the genre alias is assigned in SELECT, which runs after grouping.

An export holds 2.499,90, meaning 2499.90. Which expression returns the decimal 2499.90?

• CAST(REPLACE(REPLACE(amount_text, '.'), ',', '.') AS DECIMAL(10, 2))
• CAST(REPLACE(REPLACE(amount_text, ',', '.'), '.') AS DECIMAL(10, 2))
• CAST(REPLACE(amount_text, ',', '.') AS DECIMAL(10, 2))
• CAST(REPLACE(amount_text, '.') AS DECIMAL(10, 2))

Choosing a swap for grouping starts with 2 spellings that ought to land together. Lowercase both and find the characters where they still differ: lo-fi and lo_fi differ only at position 3. Pick one form as the target and replace the other toward it, so REPLACE(LOWER(genre_tag), '_', '-') sends both to lo-fi. Replacing toward a character that already means something else in the column would merge tags you meant to keep apart.

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.