Data Masking in SQL Server 2014

• wweraw25

  SSC Eights!

  Points: 953

  More actions

  January 16, 2017 at 11:21 am

  #333999

  There is much easier way to mask data in SQL Server 2016.

  But whats the way to mask data in SQL Server 2014 using Transact SQL.

  I want to mask number, Name and Email Address.

  Thanks in Advance.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  January 16, 2017 at 1:08 pm

  #1923047

  you'll need to use good old fashioned strings or REPLACE functions.
  and you need to take into account the data types....you said number, but is it a string representing a number, so you can mask it?

  
/*
IDStarFirstNameStarLastNameEmail
0G***B*************@****.com
0M******R*************@****.com
0C********S***********@****.com
0R*****L*********@****.com
0S*****F*******@****.com
*/
;WITH MyCTE([ID],[StarFirstName],[StarLastName],[Gender],[Address1],[City],[State],[ZipCode])
AS
(
SELECT '1','Gene','Bergman','M','2615 Gainsboro Road',' Plantation','FL','33392' UNION ALL
SELECT '2','Michael','Roberts','M','42987 DarkSlateBlue Avenue',' Sunrise','FL','33335' UNION ALL
SELECT '3','Christoph','Spacey','M','27174 Green Avenue',' Sunrise','FL','33331' UNION ALL
SELECT '4','Robert','Leigh','M','44720 Seashell Avenue',' Sunrise','FL','33333' UNION ALL
SELECT '5','Samuel','Ford','M','39710 Cornsilk Road',' Sunrise','FL','33315'
)
SELECT
0 [ID],
LEFT([StarFirstName],1) + REPLICATE ('*',LEN([StarFirstName])-1) AS [StarFirstName],
LEFT([StarLastName],1) + REPLICATE ('*',LEN([StarLastName]) -1) AS [StarLastName],
REPLICATE ('*',LEN([StarLastName])) + '@****.com' AS Email
FROM MyCTE


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!