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 - Create a inline table value function
-- Select a test database
-- Remove if it exists
IF OBJECT_ID('ufn_Mask_Numbers') > 0
DROP FUNCTION ufn_Mask_Numbers
-- Create the ITVF
CREATE FUNCTION ufn_Mask_Numbers
(@txt_input varchar(max) = '',
@chr_mask varchar(1) = 'X')
, '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)
-- 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');
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.