Replacing Data – Part 1

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.

The output from the execution can be seen below.

replacing-data-part-1

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.

Related posts

Leave a Comment