How to hide Email in SQL using Data Masking?

Question

In SQL Server 2019, I want to select  an Email from the Users table, I wrote this query

Select Email from Users

I got result ex: Ahmed@gmail.com I want to display Email like this A****@gmail.com and print only the first character and mask the rest of the email as shown below:

Answer 1

Data Masking in SQL Server 2019

To create a data masking for an email to print only the first character and mask the rest of the email, you will need to use four functions to do this :

• REPLICATE function: repeats a string value a specified number of times.
• SUBSTRING() function: It Returns part of a character, binary, text, or image expression
• CHARINDEX function: to searches for '@' character inside a second character expression and returning the starting position of the first expression if found.
• Len function: It returns the number of characters of the specified string expression, excluding trailing spaces.

The final Data Masking query :

Select 
--select first character from Email and use replicate
SUBSTRING(Email,1,1) + REPLICATE('*',5)+ 
--function to put asterisks
SUBSTRING(Email,CHARINDEX('@',Email),len(Email)-CHARINDEX('@',Email)+1) 
--at this statement i select this part  @gmail,com and to first part to become like this A*****@gmail.com
as Emailmask
From TableName