Parse Valid Email Components
Split trimmed emails containing '@' into lowercase local_part and domain columns.
- String functions
- Filtering
- Sorting
Exercise brief
Understand the request
Identity resolution engineer A matching workflow needs normalized local and domain components while excluding missing and malformed addresses.
A matching workflow needs normalized local and domain components while excluding missing and malformed addresses. Split trimmed emails containing '@' into lowercase local_part and domain columns.
Return
- Return record_id, local_part, and domain.
- Order by record_id ascending.
Constraints
- Locate '@' dynamically rather than assuming a fixed position.
- Exclude NULL, blank, and delimiter-free email values.
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
Find the delimiter position after trimming; malformed values return no usable '@' position.
Hint 2
The local part ends immediately before the delimiter and the domain starts immediately after it.
Hint 3
Lowercase both parsed components and retain only delimiter positions greater than one.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, LOWER(SUBSTR(TRIM(email), 1, INSTR(TRIM(email), '@') - 1)) AS local_part, LOWER(SUBSTR(TRIM(email), INSTR(TRIM(email), '@') + 1)) AS domain FROM function_cases WHERE INSTR(TRIM(email), '@') > 1 ORDER BY record_id;Why this works
Robust parsing combines cleanup, validation, delimiter location, slicing, and normalization. The malformed row ensures a solution cannot blindly split every value.
Success check
Exactly five valid addresses are parsed correctly despite case and edge whitespace.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | local_part | domain |
|---|---|---|
| 101 | alice.ng | example.com |
| 104 | dave.smith | example.com |
| 105 | eve.li | example.com |
| 107 | grace | sub.example.com |
| 108 | heidi | example.com |
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.