Normalize Mixed Phone Punctuation
Remove plus signs, parentheses, spaces, dots, and dashes from each non-NULL phone.
- String functions
- Filtering
- Sorting
Exercise brief
Understand the request
Contact ingestion engineer Phone values arrive with inconsistent punctuation but the matching key must contain digits only.
Phone values arrive with inconsistent punctuation but the matching key must contain digits only. Remove plus signs, parentheses, spaces, dots, and dashes from each non-NULL phone.
Return
- Return record_id, phone, and clean_phone.
- Order by record_id ascending.
Constraints
- Use nested REPLACE calls.
- Preserve every digit and exclude NULL phones.
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
REPLACE substitutes every occurrence of the requested token.
Hint 2
Each outer REPLACE cleans the result produced by the inner call.
Hint 3
Remove -, ., spaces, (, ), and +, then order by record_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, phone, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), '.', ''), ' ', ''), '(', ''), ')', ''), '+', '') AS clean_phone FROM function_cases WHERE phone IS NOT NULL ORDER BY record_id;Why this works
Nested scalar functions form a deterministic normalization pipeline. The adversarial rows prevent a solution that handles only one punctuation style from passing.
Success check
All six available phones become punctuation-free digit strings.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | phone | clean_phone |
|---|---|---|
| 101 | (555) 010-101 | 555010101 |
| 102 | 555.010.102 | 555010102 |
| 104 | +1-555-010-104 | 1555010104 |
| 105 | 555 010 105 | 555010105 |
| 107 | 555--010--107 | 555010107 |
| 108 | (555)010108 | 555010108 |
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.