Tag Archives: cte

Remove Numbers from Text SVF, Nested vs CTE

I need to remove numbers from a string while processing some data  as part of an ETL process in a data warehouse.  I have to do this for many columns and potentially many tables so I decided to put the logic into a Scalar-Valued Function (SVF), understanding there are performance detriments when using SVFs. Generally this will be used on relatively small data sets for cleaning “codes” that will be insert into a slowly changing dimension.  I was curious about how to do this.  My instinct was to do it using nested replace functions.  I did a quick google search to see what options the collective would recommend.  I came across a post on Stack Overflow that mentioned the nested replace functions, and then someone also mentioned using a recursive Common Table Expression (CTE), which I thought was a creative suggestion.


I have a soft spot in my heart for recursive CTEs so I thought I’d try both options and see which solution performs better.  Let’s start with building the functions:

Now that we have some functions we need some test data so let’s create a simple table and populate it with simi-random data by leveraging the newid() function.

Continue reading Remove Numbers from Text SVF, Nested vs CTE