Character mask output data on select

Question

I'm using SQL Server 2008.

I would like to character mask the output data of a query.

This is my data from a column on a table when doing a select:

column1

384844033434

743423547878

111224678885

I would like an output like this:

column1

384xxxxxx434

743xxxxxx878

111xxxxxx885

How can I do this?

Answer 1

You would have to use a view, and deny all users SELECT access to the underlying table.

Your view would look something like

SELECT 
     SUBSTRING(x.SecurityNumber,1,3) + 
     'xxxxx' + 
     SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber) - 2, LEN(x.SecurityNumber))
     AS column1
FROM underlyingTable x

You could then grant your users SELECT access to just this view and have the out masked in the way you described.

If you wanted your client software to be able to insert or update data in this table, you would use an INSTEAD OF INSERT or INSTEAD OF UPDATE trigger to update the base table.