Masked Tenant Phone Numbers for the Notice Board

Easy
351.

Masked Tenant Phone Numbers for the Notice Board

EasyStringsBasics

MySQL

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

ColumnType
tenant_idint
full_namevarchar
flat_novarchar
phonebigint

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.

Example

The rows must come back in the order shown.

Tenant

tenant_idfull_nameflat_nophone
1Meera IyerB-12049876543210
2Arjun Singh RathoreA-03029123400078
3Zara KhanA-1101NULL
4Vikram RaoC-07018000012345
5Sneha DasA-01057012345609

Output

flat_nofirst_namemasked_phone
A-0105SnehaXXXXXX5609
A-0302ArjunXXXXXX0078
A-1101ZaraNULL
B-1204MeeraXXXXXX3210
C-0701VikramXXXXXX2345
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.

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

ColumnType
tenant_idint
full_namevarchar
flat_novarchar
phonebigint

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_idfull_nameflat_nophone
1Meera IyerB-12049876543210
2Arjun Singh RathoreA-03029123400078
3Zara KhanA-1101NULL
4Vikram RaoC-07018000012345
5Sneha DasA-01057012345609

Output

flat_nofirst_namemasked_phone
A-0105SnehaXXXXXX5609
A-0302ArjunXXXXXX0078
A-1101ZaraNULL
B-1204MeeraXXXXXX3210
C-0701VikramXXXXXX2345

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_no

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

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