Masked Mobile Numbers on Bill Statements

Easy
502.

Masked Mobile Numbers on Bill Statements

EasyStrings

MySQL

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

ColumnType
customer_idint
namevarchar
mobile_numbervarchar
cityvarchar

Primary key: customer_id.

mobile_number is ten digits, optionally preceded by 0 or +91.

Example

The rows must come back in the order shown.

Customer

customer_idnamemobile_numbercity
1Ananya9845012345Bengaluru
2Kabir+919900123456Mumbai
3Sneha08123456789Pune
4Vikram+917019988776Delhi
5Zara9731122309Kochi

Output

customer_idnamemasked_mobile
1Ananya98XXXXXX45
2Kabir99XXXXXX56
3Sneha81XXXXXX89
4Vikram70XXXXXX76
5Zara97XXXXXX09
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 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

ColumnType
customer_idint
namevarchar
mobile_numbervarchar
cityvarchar

Primary key: customer_id.

mobile_number is ten digits, optionally preceded by 0 or +91.

Examples

Example 1

Customer

customer_idnamemobile_numbercity
1Ananya9845012345Bengaluru
2Kabir+919900123456Mumbai
3Sneha08123456789Pune
4Vikram+917019988776Delhi
5Zara9731122309Kochi

Output

customer_idnamemasked_mobile
1Ananya98XXXXXX45
2Kabir99XXXXXX56
3Sneha81XXXXXX89
4Vikram70XXXXXX76
5Zara97XXXXXX09

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_id

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

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