Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
2k views
in SQL Queries by 16 20 26
edited by

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:

How to hide Email in SQL using Data Masking


1 Answer

0 like 0 dislike
by 16 25 39
edited by

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
If you don’t ask, the answer is always NO!
...