Masked Customer Phone Numbers on Rider Slips — SQL Problem
The printed slip a rider carries shows the customer's first name and only the last four digits of their phone number, so a lost slip leaks nothing useful.
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #152
Problem statement
The printed slip a rider carries shows the customer's first name and only the last four digits of their phone number, so a lost slip leaks nothing useful. Phone numbers are ten digits.
Return customer_id, first_name (the part of full_name before the first space) and masked_phone — six X characters followed by the last four digits, e.g. XXXXXX4821. A customer with no phone on file (NULL) gets masked_phone NULL. Order the rows by customer_id.
Tables
Table: Customer
| Column | Type |
|---|---|
| customer_id | int |
| full_name | varchar |
| phone | bigint |
| city | varchar |
Primary key: customer_id.
full_name is a first name, one space and a surname. phone is a 10-digit mobile number, or NULL.
Examples
Example 1
Customer
| customer_id | full_name | phone | city |
|---|---|---|---|
| 1 | Ananya Iyer | 9845014821 | Bengaluru |
| 2 | Farhan Khan | 7012300009 | Kochi |
| 3 | Riya Mehta | NULL | Mumbai |
| 4 | Vikram Singh | 8800112233 | Delhi |
| 5 | Sara Das | 6290050400 | Kolkata |
Output
| customer_id | first_name | masked_phone |
|---|---|---|
| 1 | Ananya | XXXXXX4821 |
| 2 | Farhan | XXXXXX0009 |
| 3 | Riya | NULL |
| 4 | Vikram | XXXXXX2233 |
| 5 | Sara | XXXXXX0400 |
How to solve Masked Customer Phone Numbers on Rider Slips
Both columns are string surgery. The first name is everything before the first space: SUBSTRING_INDEX(full_name, ' ', 1) returns the text before the first occurrence of the delimiter, and LEFT(full_name, LOCATE(' ', full_name) - 1) says the same with a position.
For the phone, a string function given a number works on its decimal digits, so RIGHT(phone, 4) is the last four digits — kept as text, so a number ending in 0009 shows 0009, not 9. Prefix six X's with CONCAT, or pad the four digits on the left to ten characters with LPAD(…, 10, 'X'); since every number has exactly ten digits, SUBSTRING(phone, 7, 4) is the same four characters.
The NULL rule comes for free: MySQL's CONCAT returns NULL when any argument is NULL, and RIGHT of NULL is NULL, so a customer without a phone gets NULL. LPAD of NULL is NULL too, but the explicit CASE makes the intent visible. One pass, no join.
Reference solution (MySQL)
SELECT customer_id,
SUBSTRING_INDEX(full_name, ' ', 1) AS first_name,
CONCAT('XXXXXX', RIGHT(phone, 4)) AS masked_phone
FROM Customer
ORDER BY customer_idAnother way
SELECT customer_id, LEFT(full_name, LOCATE(' ', full_name) - 1) AS first_name, CONCAT(REPEAT('X', 6), SUBSTRING(phone, 7, 4)) AS masked_phone FROM Customer ORDER BY customer_idAnother way
SELECT customer_id, SUBSTRING(full_name, 1, INSTR(full_name, ' ') - 1) AS first_name, CASE WHEN phone IS NULL THEN NULL ELSE LPAD(RIGHT(phone, 4), 10, 'X') END AS masked_phone FROM Customer ORDER BY customer_id← Delivered GMV of Every Dark Store · Weekend Food Orders in March 2024 →