Level 5 · Turn a pasted price sheet into numbers

Numbers typed into a spreadsheet are text until you convert them: '$1,012.50', ' 5.10 ', 'n/a'. Summing or comparing them as text is meaningless, so the first step with any pasted file is to cast each column to its real type.

CAST and TRY_CAST

CAST(x AS type) converts or fails. One bad value and the whole query errors:

SELECT CAST('$219.00' AS DECIMAL(10, 2));   -- Conversion Error

TRY_CAST(x AS type) converts or returns NULL. That is what you want for cleaning, because you can then count and inspect the NULLs instead of hunting for the row that broke the query.

Before casting, remove the formatting a human added:

TRY_CAST(REPLACE(REPLACE(TRIM(cost_text), '$', ''), ',', '') AS DECIMAL(10, 2))

'$1,012.50' becomes '1012.50' and then the number 1012.50. 'n/a' and 'TBD' become NULL. DuckDB’s cast already tolerates spaces around a number, but trimming first makes the intent clear and works in every database.

Reading the result

In the task, the Vale Velvet Loveseat (101) has no current unit_cost, so its cost_change is NULL even though the supplier quoted $1,012.50. The Oakley Jute Knit Throw (162) and the Elm Brass Floor Mirror (190) have a current cost but no usable quote. The row '21x' never reaches the result: its id does not convert, so the join drops it. That silent drop is worth reporting back to the supplier.

Timestamps to dates

Casting is not only for text. orders.order_date is a TIMESTAMP, 2025-10-31 12:05:25 for order 11500. CAST(order_date AS DATE) gives 2025-10-31, which is what you group or join on when the question is “per day”. Comparing a timestamp to a date-only value matches midnight only.

Common mistakes

• One REPLACE too few. TRY_CAST('1,012.50' AS DECIMAL(10,2)) is NULL. A forgotten comma quietly turns every price above $999 into NULL.
• Casting to INTEGER for money. Cents get rounded away. Use DECIMAL(10, 2).
• Not counting the NULLs. After a TRY_CAST, run COUNT(*) - COUNT(supplier_cost) to see how many values failed.

Dialects and SQL Server

BigQuery spells it SAFE_CAST, Snowflake has TRY_CAST and TRY_TO_NUMBER, Postgres has no safe cast (filter with a regex first). SQL Server has TRY_CAST, TRY_CONVERT, and TRY_PARSE, which understands a culture’s currency format:

SELECT TRY_CONVERT(DECIMAL(10, 2), REPLACE(REPLACE(cost_text, '$', ''), ',', '')) AS supplier_cost,
       TRY_PARSE('$1,012.50' AS MONEY USING 'en-US') AS parsed_money;