Safely Convert Dirty Quantity Text
Convert valid raw_quantity values to integers and return NULL for values that cannot be converted.
- CASE expressions
- String functions
- Type conversion
- Sorting
Exercise brief
Understand the request
Landing-zone reliability engineer A raw ingestion feed mixes valid integer text with blanks, NULLs, and malformed values that must not abort the transformation.
A raw ingestion feed mixes valid integer text with blanks, NULLs, and malformed values that must not abort the transformation. Convert valid raw_quantity values to integers and return NULL for values that cannot be converted.
Return
- Return record_id, raw_quantity, and quantity_value.
- Order by record_id ascending.
Constraints
- Use a tolerant conversion function or validate the trimmed value before an explicit CAST.
- Do not filter malformed, blank, or NULL source rows.
- Do not rely on implicit coercion of invalid text to zero.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
function_cases
record_idINTEGERfirst_nameTEXTlast_nameTEXTlabelTEXTemailTEXTbackup_emailTEXTphoneTEXTraw_unitsTEXTraw_quantityTEXTraw_statusTEXTactual_valueREALtarget_valueREALcompleted_unitsREALelapsed_hoursREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A plain CAST may fail or silently coerce malformed text depending on the engine.
Hint 2
Tolerant functions include TRY_CAST in SQL Server; other engines can validate the text before CAST or provide their own safe-conversion syntax.
Hint 3
Keep raw_quantity in the result, preserve every row, and return NULL for blank, malformed, and already NULL values.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, raw_quantity, CASE WHEN TRIM(raw_quantity) <> '' AND TRIM(raw_quantity) NOT GLOB '*[^0-9]*' THEN CAST(TRIM(raw_quantity) AS INTEGER) END AS quantity_value FROM function_cases ORDER BY record_id;Why this works
Tolerant conversion is a production ingestion pattern: preserve the raw value for observability, produce a typed value when valid, and represent conversion failure as NULL instead of terminating a batch or inventing zero.
Success check
All eight source rows remain visible; valid integer text converts correctly and every unusable value becomes NULL.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | raw_quantity | quantity_value |
|---|---|---|
| 101 | 42 | 42 |
| 102 | 17 | 17 |
| 103 | N/A | NULL |
| 104 | NULL | |
| 105 | NULL | NULL |
| 106 | 003 | 3 |
| 107 | 12x | NULL |
| 108 | 0 | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.