SQL Exercise – 21

SQL Exercise:

Mask Sensitive Data with * based on character lengths

Write a query to mask sensitive data such as email address, Phone Number and Aadhaar Number in the given table dynamically, where the number of * corresponds to the length of the characters being masked.

Create table script:

CREATE TABLE SensitiveData (
ID INT PRIMARY KEY,
FullName VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(15),
AadhaarNumber VARCHAR(12) -- Unique 12-digit Indian identification number
);

Data Insertion script:

INSERT INTO SensitiveData (ID, FullName, Email, PhoneNumber, AadhaarNumber) VALUES
(1, 'Arjun Singh', 'arjun.singh@example.com', '9876543210', '123456789012'),
(2, 'Priya Sharma', 'priya.sharma@example.com', '8765432109', '987654321098'),
(3, 'Rahul Verma', 'rahul.verma@example.com', '7654321098', '456789012345');

Solution:

SELECT 
ID,
FullName,
CONCAT(
LEFT(Email, 1), 
REPLICATE('*', CHARINDEX('@', Email) - 2), 
SUBSTRING(Email, CHARINDEX('@', Email), LEN(Email) - CHARINDEX('@', Email) + 1)
) AS MaskedEmail,
CONCAT(
REPLICATE('*', LEN(PhoneNumber) - 4), 
RIGHT(PhoneNumber, 4)
) AS MaskedPhone,
CONCAT(
REPLICATE('*', LEN(AadhaarNumber) - 4), 
RIGHT(AadhaarNumber, 4)
) AS MaskedAadhaar
FROM EmployeeData;

Output:

 

Explanation:

• Email Masking:
  • SUBSTRING(Email, CHARINDEX(‘@’, Email), LEN(Email) – CHARINDEX(‘@’, Email) + 1) ensures the domain part is retained.
    • CHARINDEX(‘@’, Email) finds the position of @.
    • LEN(Email) – CHARINDEX(‘@’, Email) + 1 calculates the length of the domain part.
• Phone  Masking:
  • CONCAT(REPLICATE(‘*’, LEN(PhoneNumber) – 4),RIGHT(PhoneNumber, 4))
    • The REPLICATE function generates a string of asterisks (*) based on the calculated number.
    • RIGHT(PhoneNumber, 4): This function extracts the last 4 digits of the phone number.
    • The CONCAT function combines the generated asterisks with the last 4 digits.
• Aadhaar Masking:
  • CONCAT(REPLICATE(‘*’, LEN(AadhaarNumber) – 4),RIGHT(AadhaarNumber, 4))
    • The REPLICATE function generates a string of asterisks (*) based on the calculated number.
    • RIGHT(AadhaarNumber, 4): This function extracts the last 4 digits of the Aadhaar number.
    • The CONCAT function combines the generated asterisks with the last 4 digits.