Parse Currency Amounts — Solution & ExplanationProblems / Parse Currency Amounts / EditorialApproach

Clean the string: replace(replace(amount, '$', ''), ',', '') strips the currency symbol and every thousands separator (replace substitutes all occurrences). Equivalently, one regex pass: regexp_replace(amount, '[$,]', '', 'g') — inside a character class $ is a literal, and DuckDB needs the 'g' flag to replace globally.

Cast the cleaned string to DOUBLE (or DECIMAL). Now '$18,750.00' is the number 18750.0.

SUM per department and ROUND(..., 2).

Order by department.

Key InsightDisplay formats are for humans; storage should be numeric. When a legacy system hands you formatted strings, parsing is a two-step discipline: normalize (strip every non-numeric decoration, not just the first occurrence) then cast. The sneaky bug is a partial cleanup — removing $ but forgetting , — which errors (or NULLs) only on amounts ≥ $1,000, so small test samples pass while the real total silently drops the largest expenses.Hints

Two nested replace() calls do the job: replace(replace(amount, '$', ''), ',', ''). Both replace ALL occurrences, so '$18,750.00' loses its comma too.

A single regexp_replace also works — the character class [$,] matches either character ('$' is literal inside a character class). In DuckDB SQL remember the 'g' flag: regexp_replace(amount, '[$,]', '', 'g').

After cleaning, CAST to DOUBLE (or DECIMAL), SUM per department, and round the total to 2 decimals. In PySpark: F.regexp_replace(F.col('amount'), '[$,]', '').cast('double').

Solve Parse Currency Amounts yourself →