Write a query to return every contacts.contact_id with a masked_email. Lowercase the address. Before the first @, keep the first character and replace each remaining character with *; a one-character local part gets no asterisks. Leave the domain unchanged except for lowercasing it.
Return NULL when email is NULL or contains no @.
Ordering: Sort by contact_id in ascending order.
Returncontact_idmasked_email
About this problem
You practise rebuilding a string from pieces you extracted, with the length of one piece deciding the shape of the output – the everyday work behind redacting a column for a ticket, a screenshot or a shared export. From contacts you return a masked_email whose local part keeps its first character and replaces every other one with an asterisk, leaving the domain alone and returning NULL for any address you could not parse. The skill is getting the count right and refusing to guess when the delimiter is missing.
There are two quiet failures here, and neither raises an error. The first is the off-by-one: repeat('*', length(local)) gives one asterisk too many, and j****@example.com looks entirely plausible next to j***@example.com unless you compare lengths with the original. The second is contact 8, whose address has no @ because someone typed a full stop. split_part does not object – it returns the whole value as the first piece and an empty string as the second – so an unguarded query cheerfully publishes d**************@, which exposes the length of the real value and ends in a dangling @. That is worse than returning nothing, which is why the prompt asks for NULL: when a value does not have the shape you assumed, say so rather than masking it into something that looks deliberate.
Read the lesson: String Functions (P12) →