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 function. However, this function replaces every occurrence of search string B in source string A with replacement string C. See my previous blog article which shows how to use this function.
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 that attests to this fact.
Lets create a inline table valued function to solve this problem.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
/* 1 - Create a inline table value function */ -- Select a test database USE AdventureWorks2012; GO -- Remove if it exists IF OBJECT_ID('ufn_Mask_Numbers') > 0 DROP FUNCTION ufn_Mask_Numbers GO -- Create the ITVF CREATE FUNCTION ufn_Mask_Numbers (@txt_input varchar(max) = '', @chr_mask varchar(1) = 'X') RETURNS TABLE AS RETURN ( SElECT replace( replace( replace( replace( replace( replace( replace( replace( replace( replace(@txt_input , '0', @chr_mask) , '1', @chr_mask) , '2', @chr_mask) , '3', @chr_mask) , '4', @chr_mask) , '5', @chr_mask) , '6', @chr_mask) , '7', @chr_mask) , '8', @chr_mask) , '9', @chr_mask) AS txt_masked ); GO -- Mask the top five rows SELECT top 5 c.*, txt_masked as MaskedNumber FROM [Sales].[CreditCard] as c CROSS APPLY ufn_Mask_Numbers(CardNumber, 'O'); GO |
The output from the execution can be seen below.
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.
In the next article, I will be talking about how to create a super replace function that takes a associative array of key value pairs and a text string as input. It applies all translations to the string and returns the result as output.
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.