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.


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