Patient Mobile Numbers That Cannot Get SMS Reminders
Appointment reminders go out by SMS, and the gateway accepts only a plain Indian mobile number: exactly 10 digits, the first one 6, 7, 8 or 9 — no spaces,…
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #202
Problem statement
Appointment reminders go out by SMS, and the gateway accepts only a plain Indian mobile number: exactly 10 digits, the first one 6, 7, 8 or 9 — no spaces, no +91, nothing else. Reception typed some numbers in other ways, and some patients gave none.
Return patient_id, name and mobile of every patient whose number the gateway would refuse, including a NULL mobile, ordered by patient_id.
Tables
Table: Patient
| Column | Type |
|---|---|
| patient_id | int |
| name | varchar |
| mobile | varchar |
Primary key: patient_id.
mobile is free text as typed at registration, or NULL when the patient gave no number.
Examples
Example 1
Patient
| patient_id | name | mobile |
|---|---|---|
| 1 | Aarav Sharma | 9845012345 |
| 2 | Diya Reddy | +919845012346 |
| 3 | Kabir Singh | 98450 12347 |
| 4 | Meera Das | NULL |
| 5 | Nikhil Rao | 5123456789 |
| 6 | Pooja Patel | 7012345678 |
| 7 | Farhan Khan | 984501234 |
Output
| patient_id | name | mobile |
|---|---|---|
| 2 | Diya Reddy | +919845012346 |
| 3 | Kabir Singh | 98450 12347 |
| 4 | Meera Das | NULL |
| 5 | Nikhil Rao | 5123456789 |
| 7 | Farhan Khan | 984501234 |
How to solve Patient Mobile Numbers That Cannot Get SMS Reminders
A valid number is easiest to describe as a regular expression: ^[6-9][0-9]{9}$ — one leading digit from 6 to 9, then exactly nine more digits, and nothing before or after thanks to the anchors. Every patient whose number does not match is refused by the gateway: a +91 prefix, a space in the middle, a landline-style leading 0 or 5, nine or eleven digits.
The trap is the NULL. mobile NOT REGEXP … on a NULL is NULL, which WHERE treats as false, so a patient with no number would be dropped — yet they are exactly the people who will never get a reminder. The explicit mobile IS NULL branch keeps them. A CASE that maps a match to 0 and everything else (including NULL) to 1 gets the same effect in one expression.
Without regular expressions the rule splits into three tests: length 10, first character in 6–9, and no character outside 0–9 (NOT REGEXP '[^0-9]', or a chain of REPLACE calls). One pass over the table.
Reference solution (MySQL)
SELECT patient_id, name, mobile
FROM Patient
WHERE mobile IS NULL
OR mobile NOT REGEXP '^[6-9][0-9]{9}$'
ORDER BY patient_idAnother way
SELECT patient_id, name, mobile FROM Patient WHERE NOT (mobile IS NOT NULL AND CHAR_LENGTH(mobile) = 10 AND LEFT(mobile, 1) IN ('6', '7', '8', '9') AND mobile NOT REGEXP '[^0-9]') ORDER BY patient_idAnother way
SELECT patient_id, name, mobile FROM Patient WHERE CASE WHEN mobile REGEXP '^[6-9][0-9]{9}$' THEN 0 ELSE 1 END = 1 ORDER BY patient_id← Medicine Batches Expiring Within 90 Days · Label Each Insurance Claim by How It Settled →