Masked Card Numbers for Card Statements

Easy
51.

Masked Card Numbers for Card Statements

EasyStrings

MySQL

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

ColumnTypeValues
card_idint
holder_namevarchar
card_numbervarchar
networkenumVISA, MASTERCARD, RUPAY

Primary key: card_id.

card_number holds 16 digits, stored either as one run or in four groups separated by single spaces.

Example

The rows must come back in the order shown.

CreditCard

card_idholder_namecard_numbernetwork
1Aarav Sharma4111222233334821VISA
2Diya Nair5326 7788 1200 0457MASTERCARD
3Kabir Khan6521000011119900RUPAY
4Sara Menon4532 0101 2020 3036VISA

Output

card_idembossed_namemasked_number
1AARAV SHARMAXXXX-XXXX-XXXX-4821
2DIYA NAIRXXXX-XXXX-XXXX-0457
3KABIR KHANXXXX-XXXX-XXXX-9900
4SARA MENONXXXX-XXXX-XXXX-3036
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 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

ColumnType
card_idint
holder_namevarchar
card_numbervarchar
networkenum(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_idholder_namecard_numbernetwork
1Aarav Sharma4111222233334821VISA
2Diya Nair5326 7788 1200 0457MASTERCARD
3Kabir Khan6521000011119900RUPAY
4Sara Menon4532 0101 2020 3036VISA

Output

card_idembossed_namemasked_number
1AARAV SHARMAXXXX-XXXX-XXXX-4821
2DIYA NAIRXXXX-XXXX-XXXX-0457
3KABIR KHANXXXX-XXXX-XXXX-9900
4SARA MENONXXXX-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_id

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

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