Audit Unusable Active Contact Numbers
Find active contacts whose phone is NULL, empty, or whitespace-only.
- String functions
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Contact data auditor An outreach audit must identify active contacts whose phone cannot be used, regardless of how missing text was stored.
Return active contact records whose phone is NULL, empty, or whitespace-only.
Return
- Return contact_id, display_name, phone in this exact left-to-right order.
Constraints
- Apply the active-status rule to a grouped NULL-or-blank phone condition.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
contact_records
contact_idINTEGERdisplay_nameVARCHAR(100)phoneVARCHAR(30)is_activeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Missing text can be represented by NULL, an empty string, or a string containing only spaces.
Hint 2
Require an active record, then group the NULL check and the trimmed-empty check inside parentheses.
Hint 3
SELECT contact_id, display_name, phone FROM contact_records WHERE is_active = /* active flag */ AND (phone /* NULL predicate */ OR TRIM(phone) = /* empty text */) ORDER BY contact_id;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT contact_id, display_name, phone FROM contact_records WHERE is_active = 1 AND (phone IS NULL OR TRIM(phone) = '') ORDER BY contact_id;Why this works
A reliable data-quality filter treats NULL, empty text, and whitespace-only text as distinct storage states that all represent an unusable phone number. The parentheses are essential because the active-status rule must apply to both missing-value alternatives. `phone = NULL` never matches, while checking only `IS NULL` misses the empty and whitespace fixtures. `TRIM` is portable across the executable engines; Oracle treats an empty character string as NULL and needs a NULL test on the trimmed value.
Success check
Every returned contact is active and has no usable phone text.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| contact_id | display_name | phone |
|---|---|---|
| 1 | Alice Johnson | NULL |
| 2 | ALICE JOHNSON | |
| 3 | alice johnson |
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.