Patient Mobile Numbers That Cannot Get SMS Reminders

Easy
202.

Patient Mobile Numbers That Cannot Get SMS Reminders

EasyStringsBasics

MySQL

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

ColumnType
patient_idint
namevarchar
mobilevarchar

Primary key: patient_id.

mobile is free text as typed at registration, or NULL when the patient gave no number.

Example

The rows must come back in the order shown.

Patient

patient_idnamemobile
1Aarav Sharma9845012345
2Diya Reddy+919845012346
3Kabir Singh98450 12347
4Meera DasNULL
5Nikhil Rao5123456789
6Pooja Patel7012345678
7Farhan Khan984501234

Output

patient_idnamemobile
2Diya Reddy+919845012346
3Kabir Singh98450 12347
4Meera DasNULL
5Nikhil Rao5123456789
7Farhan Khan984501234
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
patient_idint
namevarchar
mobilevarchar

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_idnamemobile
1Aarav Sharma9845012345
2Diya Reddy+919845012346
3Kabir Singh98450 12347
4Meera DasNULL
5Nikhil Rao5123456789
6Pooja Patel7012345678
7Farhan Khan984501234

Output

patient_idnamemobile
2Diya Reddy+919845012346
3Kabir Singh98450 12347
4Meera DasNULL
5Nikhil Rao5123456789
7Farhan Khan984501234

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_id

Another 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_id

Another 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 →