Masked Mobile Numbers on Bill Statements — SQL Problem
Printed bill statements must not show a customer's full mobile number.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #502
Problem statement
Printed bill statements must not show a customer's full mobile number. The CRM stores numbers in three shapes: ten digits (9845012345), with a trunk prefix (09845012345) or with the country code (+919845012345). The last ten characters are always the number itself.
Return customer_id, name and masked_mobile: the first two and last two digits of the ten-digit number with the six digits between replaced by XXXXXX, e.g. 98XXXXXX45. Order by customer_id.
Tables
Table: Customer
| Column | Type |
|---|---|
| customer_id | int |
| name | varchar |
| mobile_number | varchar |
| city | varchar |
Primary key: customer_id.
mobile_number is ten digits, optionally preceded by 0 or +91.
Examples
Example 1
Customer
| customer_id | name | mobile_number | city |
|---|---|---|---|
| 1 | Ananya | 9845012345 | Bengaluru |
| 2 | Kabir | +919900123456 | Mumbai |
| 3 | Sneha | 08123456789 | Pune |
| 4 | Vikram | +917019988776 | Delhi |
| 5 | Zara | 9731122309 | Kochi |
Output
| customer_id | name | masked_mobile |
|---|---|---|
| 1 | Ananya | 98XXXXXX45 |
| 2 | Kabir | 99XXXXXX56 |
| 3 | Sneha | 81XXXXXX89 |
| 4 | Vikram | 70XXXXXX76 |
| 5 | Zara | 97XXXXXX09 |
How to solve Masked Mobile Numbers on Bill Statements
The trick is to normalise before masking. Whatever prefix a number carries — none, 0 or +91 — its last ten characters are the subscriber's number, so RIGHT(mobile_number, 10) gives a clean ten-digit string for every row.
From there the mask is three pieces: the first two digits of the clean number (LEFT(…, 2)), the literal XXXXXX, and the last two digits, which are also the last two of the raw value (RIGHT(mobile_number, 2)). CONCAT joins them.
Positions counted from the end work just as well: the clean number starts at character CHAR_LENGTH(mobile_number) - 9. A CASE on the three shapes is the most literal reading of the statement, but it has to know every format in advance, while the RIGHT version keeps working if a fourth prefix ever appears. Every form is a per-row string operation, so the cost is one pass.
Reference solution (MySQL)
SELECT customer_id, name,
CONCAT(LEFT(RIGHT(mobile_number, 10), 2), 'XXXXXX', RIGHT(mobile_number, 2)) AS masked_mobile
FROM Customer
ORDER BY customer_idAnother way
SELECT customer_id, name,
CONCAT(SUBSTRING(mobile_number, CHAR_LENGTH(mobile_number) - 9, 2), REPEAT('X', 6),
SUBSTRING(mobile_number, CHAR_LENGTH(mobile_number) - 1, 2)) AS masked_mobile
FROM Customer
ORDER BY customer_idAnother way
SELECT customer_id, name,
CASE
WHEN mobile_number LIKE '+91%' THEN CONCAT(SUBSTRING(mobile_number, 4, 2), 'XXXXXX', RIGHT(mobile_number, 2))
WHEN CHAR_LENGTH(mobile_number) = 11 THEN CONCAT(SUBSTRING(mobile_number, 2, 2), 'XXXXXX', RIGHT(mobile_number, 2))
ELSE CONCAT(LEFT(mobile_number, 2), 'XXXXXX', RIGHT(mobile_number, 2))
END AS masked_mobile
FROM Customer
ORDER BY customer_id← Cell Tower Dropped-Call Health Check · Completed Port-Outs by Recipient Operator →