Masked Card Numbers for Card Statements — SQL Problem
Card statements may never show a full card number. The card team prints only the last four digits behind a fixed mask, and the holder's name the way it is…
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #51
Problem statement
Card statements may never show a full card number. The card team prints only the last four digits behind a fixed mask, and the holder's name the way it is embossed — in capitals. Some card numbers were captured with spaces between the groups of four digits.
Return card_id, embossed_name (the holder name in upper case) and masked_number, which is XXXX-XXXX-XXXX- followed by the card's last four digits. Order the rows by card_id.
Tables
Table: CreditCard
| Column | Type |
|---|---|
| card_id | int |
| holder_name | varchar |
| card_number | varchar |
| network | enum(VISA, MASTERCARD, RUPAY) |
Primary key: card_id.
card_number holds 16 digits, stored either as one run or in four groups separated by single spaces.
Examples
Example 1
CreditCard
| card_id | holder_name | card_number | network |
|---|---|---|---|
| 1 | Aarav Sharma | 4111222233334821 | VISA |
| 2 | Diya Nair | 5326 7788 1200 0457 | MASTERCARD |
| 3 | Kabir Khan | 6521000011119900 | RUPAY |
| 4 | Sara Menon | 4532 0101 2020 3036 | VISA |
Output
| card_id | embossed_name | masked_number |
|---|---|---|
| 1 | AARAV SHARMA | XXXX-XXXX-XXXX-4821 |
| 2 | DIYA NAIR | XXXX-XXXX-XXXX-0457 |
| 3 | KABIR KHAN | XXXX-XXXX-XXXX-9900 |
| 4 | SARA MENON | XXXX-XXXX-XXXX-3036 |
How to solve Masked Card Numbers for Card Statements
The work is all string functions on each row. The holder name becomes UPPER(holder_name). For the mask, notice that the spaces in some card numbers only ever separate the groups of four, so whether a number is stored as 4111222233334821 or 4111 2222 3333 4821, its last four characters are its last four digits — RIGHT(card_number, 4) is enough, and CONCAT('XXXX-XXXX-XXXX-', …) adds the fixed prefix.
If the spacing were less regular, the safe version strips the spaces first with REPLACE(card_number, ' ', '') and then takes characters 13 to 16 with SUBSTRING — the first alternative does exactly that. Another way is SUBSTRING(card_number, CHAR_LENGTH(card_number) - 3), which starts four characters from the end.
Masking in the query is also good practice in real systems: the full number never needs to leave the database for the statement printer. The query is a single scan with no sort beyond the primary key order.
Reference solution (MySQL)
SELECT card_id,
UPPER(holder_name) AS embossed_name,
CONCAT('XXXX-XXXX-XXXX-', RIGHT(card_number, 4)) AS masked_number
FROM CreditCard
ORDER BY card_idAnother way
SELECT card_id, UCASE(holder_name) AS embossed_name, CONCAT(REPEAT('XXXX-', 3), SUBSTRING(REPLACE(card_number, ' ', ''), 13, 4)) AS masked_number FROM CreditCard ORDER BY card_idAnother way
SELECT card_id, UPPER(holder_name) AS embossed_name, CONCAT('XXXX-XXXX-XXXX-', SUBSTRING(card_number, CHAR_LENGTH(card_number) - 3)) AS masked_number FROM CreditCard ORDER BY card_id← Active Deposits Held at Each Branch · Fixed Deposits Maturing in March 2025 →