PhonePe: Masked UPI IDs on Payment Receipts — SQL Problem
A shared payment receipt should not reveal the full UPI ID of the person paid.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #877
Problem statement
A shared payment receipt should not reveal the full UPI ID of the person paid. The receipts team masks each UPI ID (vpa, written handle@provider) before printing it.
Return payee_id and masked_vpa: keep the first two characters of the handle, replace every other character of the handle with *, and keep the @ and the provider unchanged. So ravi.k99@ybl becomes ra******@ybl, and a two-character handle such as om@axl stays as it is. Every vpa has exactly one @ and a handle of at least two characters. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: Payee
| Column | Type |
|---|---|
| payee_id | int |
| display_name | varchar |
| vpa | varchar |
Primary key: payee_id.
One row per saved payee; vpa is the UPI ID, a handle and a provider joined by @.
Examples
Example 1
Payee
| payee_id | display_name | vpa |
|---|---|---|
| 1 | Ravi Kumar | ravi.k99@ybl |
| 2 | Anita Shah | 9876543210@ibl |
| 3 | Om Prakash | om@axl |
| 4 | Sneha Rao | sneharao@okaxis |
| 5 | Dev Menon | dev@upi |
Output
| payee_id | masked_vpa |
|---|---|
| 1 | ra******@ybl |
| 2 | 98********@ibl |
| 3 | om@axl |
| 4 | sn******@okaxis |
| 5 | de*@upi |
How to solve PhonePe: Masked UPI IDs on Payment Receipts
Masking is string surgery in three pieces glued with CONCAT. The first piece is LEFT(vpa, 2). The last piece is everything from the @ on, SUBSTRING(vpa, LOCATE('@', vpa)), which keeps the provider exactly as stored. The middle is one * per hidden character.
How many characters are hidden? The handle ends just before the @, so its length is LOCATE('@', vpa) - 1, and two of those are shown: LOCATE('@', vpa) - 3 stars. For a two-character handle that is zero, and REPEAT('*', 0) is the empty string, so om@axl comes out unchanged — the edge case the statement calls out. A numeric handle is masked the same way as a name; nothing depends on letter case.
The alternatives cut the parts with SUBSTRING_INDEX (the text before the first @, and after the last one — the same @ here) or build the mask with RPAD('', n, '*'). Each row is handled on its own, so the cost is one scan.
Reference solution (MySQL)
SELECT payee_id,
CONCAT(LEFT(vpa, 2), REPEAT('*', LOCATE('@', vpa) - 3), SUBSTRING(vpa, LOCATE('@', vpa))) AS masked_vpa
FROM PayeeAnother way
SELECT payee_id, CONCAT(SUBSTRING(vpa, 1, 2), REPEAT('*', CHAR_LENGTH(SUBSTRING_INDEX(vpa, '@', 1)) - 2), '@', SUBSTRING_INDEX(vpa, '@', -1)) AS masked_vpa FROM PayeeAnother way
SELECT payee_id, CONCAT(LEFT(vpa, 2), RPAD('', INSTR(vpa, '@') - 3, '*'), RIGHT(vpa, CHAR_LENGTH(vpa) - INSTR(vpa, '@') + 1)) AS masked_vpa FROM Payee← PhonePe: In-App Insurance Premium by Product Line in 2025 · PhonePe: Collect Requests Still Pending After Their Expiry →