Masked Customer Phone Numbers on Rider Slips

Easy
152.

Masked Customer Phone Numbers on Rider Slips

EasyStringsBasics

MySQL

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

ColumnType
customer_idint
full_namevarchar
phonebigint
cityvarchar

Primary key: customer_id.

full_name is a first name, one space and a surname. phone is a 10-digit mobile number, or NULL.

Example

The rows must come back in the order shown.

Customer

customer_idfull_namephonecity
1Ananya Iyer9845014821Bengaluru
2Farhan Khan7012300009Kochi
3Riya MehtaNULLMumbai
4Vikram Singh8800112233Delhi
5Sara Das6290050400Kolkata

Output

customer_idfirst_namemasked_phone
1AnanyaXXXXXX4821
2FarhanXXXXXX0009
3RiyaNULL
4VikramXXXXXX2233
5SaraXXXXXX0400
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 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

ColumnType
customer_idint
full_namevarchar
phonebigint
cityvarchar

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_idfull_namephonecity
1Ananya Iyer9845014821Bengaluru
2Farhan Khan7012300009Kochi
3Riya MehtaNULLMumbai
4Vikram Singh8800112233Delhi
5Sara Das6290050400Kolkata

Output

customer_idfirst_namemasked_phone
1AnanyaXXXXXX4821
2FarhanXXXXXX0009
3RiyaNULL
4VikramXXXXXX2233
5SaraXXXXXX0400

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_id

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

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