Masked Tenant Phone Numbers for the Notice Board
A housing society pins an emergency-contact list on the lobby notice board.
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #351
Problem statement
A housing society pins an emergency-contact list on the lobby notice board. For privacy it shows only each tenant's first name and a phone number with all but the last four digits replaced by X — 9876543210 becomes XXXXXX3210.
Return flat_no, first_name (the part of full_name before the first space) and masked_phone, ordered by flat_no. A tenant with no phone on record gets NULL in masked_phone. Every phone is exactly 10 digits and every full name has at least one space.
Tables
Table: Tenant
| Column | Type |
|---|---|
| tenant_id | int |
| full_name | varchar |
| flat_no | varchar |
| phone | bigint |
Primary key: tenant_id.
One tenant per flat; flat_no is the wing letter, a hyphen and the flat number (B-1204). phone is NULL when the tenant did not share one.
Examples
Example 1
Tenant
| tenant_id | full_name | flat_no | phone |
|---|---|---|---|
| 1 | Meera Iyer | B-1204 | 9876543210 |
| 2 | Arjun Singh Rathore | A-0302 | 9123400078 |
| 3 | Zara Khan | A-1101 | NULL |
| 4 | Vikram Rao | C-0701 | 8000012345 |
| 5 | Sneha Das | A-0105 | 7012345609 |
Output
| flat_no | first_name | masked_phone |
|---|---|---|
| A-0105 | Sneha | XXXXXX5609 |
| A-0302 | Arjun | XXXXXX0078 |
| A-1101 | Zara | NULL |
| B-1204 | Meera | XXXXXX3210 |
| C-0701 | Vikram | XXXXXX2345 |
How to solve Masked Tenant Phone Numbers for the Notice Board
Two string operations and one concatenation. SUBSTRING_INDEX(full_name, ' ', 1) returns everything before the first space, so "Arjun Singh Rathore" becomes "Arjun" — the count of 1 means "up to the first delimiter", which is exactly the first name even when the name has three parts. LEFT(full_name, LOCATE(' ', full_name) - 1) does the same by finding the space's position.
For the phone, RIGHT(phone, 4) takes the last four characters of the number's text, keeping leading zeros of that tail (8000012345 ends in 2345, 9123400078 in 0078). Prefixing six X characters gives a ten-character mask. Because CONCAT returns NULL if any argument is NULL, a tenant without a phone gets NULL without a CASE — the second alternative spells the CASE out anyway.
The order is by flat_no as text; the zero-padded flat numbers make that the natural order within a wing. One pass over the table.
Reference solution (MySQL)
SELECT flat_no,
SUBSTRING_INDEX(full_name, ' ', 1) AS first_name,
CONCAT('XXXXXX', RIGHT(phone, 4)) AS masked_phone
FROM Tenant
ORDER BY flat_noAnother way
SELECT flat_no, LEFT(full_name, LOCATE(' ', full_name) - 1) AS first_name, CASE WHEN phone IS NULL THEN NULL ELSE CONCAT(REPEAT('X', 6), SUBSTRING(phone, 7, 4)) END AS masked_phone FROM Tenant ORDER BY flat_noAnother way
SELECT flat_no, SUBSTRING(full_name, 1, INSTR(full_name, ' ') - 1) AS first_name, CONCAT(LPAD('', 6, 'X'), SUBSTR(phone, -4)) AS masked_phone FROM Tenant ORDER BY flat_no← Leads Never Taken on a Completed Site Visit · Maintenance Tickets Breaching the Seven-Day SLA →