Write a query to return every staging_orders.order_ref with amount_text converted to a numeric amount. Treat commas as thousands separators and preserve decimal values.
If the value cannot be read as a number, return NULL for amount. Keep the row and do not let the conversion fail.
Ordering: Sort by order_ref in ascending order.
Returnorder_refamount
About this problem
You practise the first step of nearly every staging load: turning a text column into a number without letting one unreadable value stop the query. The amounts in staging_orders arrive with commas as thousands separators and a couple of rows hold n/a or nothing at all, so you return amount as a real number and NULL where the value cannot be read. The skill is knowing what a cast will and will not forgive.
The thing that surprises people is which characters a cast ignores. Surrounding spaces are fine – TRY_CAST(' 2400 ' AS DECIMAL(12,2)) gives 2400 – so ' 2,400 ' looks like it should work, and it returns NULL anyway. The comma is the whole problem. The other two mistakes are quieter. Reaching for CAST instead of TRY_CAST turns one n/a into a failed query, which at least tells you something is wrong; choosing INTEGER instead of a decimal type silently rounds 99.50 to 100, and nothing tells you at all. And if you decide to strip every non-digit with a regular expression rather than just the comma, 99.50 becomes 9950: you have removed the decimal point along with the separator.
Read the lesson: Data Types & CAST (P13) →