{"id":6806,"date":"2013-12-12T00:00:02","date_gmt":"2013-12-12T00:00:02","guid":{"rendered":"http:\/\/craftydba.com\/?p=6806"},"modified":"2016-04-20T01:58:29","modified_gmt":"2016-04-20T01:58:29","slug":"masking-data","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6806","title":{"rendered":"Replacing Data – Part 1"},"content":{"rendered":"

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW<\/a>. <\/p>\n

Many times, you are just helping and\/or teaching younger SQL Server developers and\/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer.<\/p>\n

One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string?<\/p>\n

The Transaction SQL language does come with a REPLACE() string function. However, this function replaces every occurrence of search string B in source string A with replacement string C. See my previous blog article<\/a> which shows how to use this function.<\/p>\n

What we really want is some type of function that replaces numbers 0 to 9 with a defined masking character. In general, in-line table valued functions perform very well. See Wayne Sheffield’s blog article<\/a> that attests to this fact.<\/p>\n

Lets create a inline table valued function to solve this problem.<\/p>\n

\r\n\/*\r\n   1 - Create a inline table value function \r\n*\/\r\n\r\n-- Select a test database\r\nUSE AdventureWorks2012;\r\nGO\r\n\r\n-- Remove if it exists\r\nIF OBJECT_ID('ufn_Mask_Numbers') > 0\r\nDROP FUNCTION ufn_Mask_Numbers\r\nGO\r\n\r\n-- Create the ITVF\r\nCREATE FUNCTION ufn_Mask_Numbers \r\n  (@txt_input varchar(max) = '', \r\n   @chr_mask varchar(1) = 'X')\r\nRETURNS TABLE\r\nAS\r\nRETURN \r\n(\r\n SElECT \r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(\r\n   replace(@txt_input\r\n         , '0', @chr_mask)\r\n         , '1', @chr_mask)\r\n\t , '2', @chr_mask)\r\n\t , '3', @chr_mask)\r\n\t , '4', @chr_mask)\r\n\t , '5', @chr_mask)\r\n\t , '6', @chr_mask)\r\n\t , '7', @chr_mask)\r\n\t , '8', @chr_mask)\r\n\t , '9', @chr_mask)\r\n  AS txt_masked\r\n);\r\nGO\r\n\r\n-- Mask the top five rows\r\nSELECT top 5 c.*, txt_masked as MaskedNumber\r\nFROM [Sales].[CreditCard] as c CROSS APPLY ufn_Mask_Numbers(CardNumber, 'O');\r\nGO\r\n<\/pre>\n
<\/span><\/p>\n
The output from the execution can be seen below.<\/p>\n
\"replacing-data-part-1\"<\/a><\/p>\n
This function works great for masking numeric data.  However, there are many times in which we want to replace a list of words A with a list of words B.  In that case, this function will not do.<\/p>\n
In the next article, I will be talking about how to create a super replace function that takes a associative array<\/a> of key value pairs and a text string as input.  It applies all translations to the string and returns the result as output.  <\/p>\n
I will use this new function to re-implement the mask numbers function.  The best part is that this function can be applied to more than just number masking.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and\/or teaching younger SQL Server developers and\/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? The Transaction SQL language does come with a REPLACE() string…<\/p>\n","protected":false},"author":1,"featured_media":6826,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,15,1006,329,29,1007],"class_list":["post-6806","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-dev","tag-database-developer","tag-john-f-miner-iii","tag-masking-data","tag-replace","tag-tsql","tag-tvf"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6806","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6806"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6806\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6826"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6806"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6806"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6806"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}