I have a column called Email Address that I want to mask the display of the addresses with *.

Something like this:

[email protected] --> a****[email protected]
[email protected] --> a****[email protected]
[email protected] --> a*****[email protected]

How can I achieve this? How to implement the stuff or replace function?

CodePudding user response：

Hiding data in SQL Server can be obtained by using data masks. For instance when using:

ALTER COLUMN EmailAddress NVARCHAR(100) MASKED WITH (FUNCTION = 'email()')

then [email protected] will be displayed as [email protected].

Note it will always result in ending with .com, no matter what the actual top level domain of the email address is.

You can also use partial masking:

ALTER COLUMN EmailAddress NVARCHAR(100) MASKED WITH (FUNCTION = 'partial(3, "XXXXX", 9)')

then [email protected] will be displayed as aarXXXXXworks.com.

Both email() and partial() won't result exactly in what you try to obtain though, which doesn't seem to be possible, because the position of the @ depends on the email address.

These articles describe data masking pretty well:

• https://www.sqlshack.com/dynamic-data-masking-in-sql-server-2/
• https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16

CodePudding user response：

If you are just concerned with masking the email addresses when selecting them from the table, you can do that numerous ways using SQL Server's existing string functions.

One way would be to use a combination of stuff and charindex

with t as ( -- Sample data
  select * from (values
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]')
  )e(email)
)  
select email, Stuff(email, 2, p, Replicate('*', p)) masked
from t
cross apply(values(CharIndex('@', email) - 3))a(p);