TSQL Remove Characters from Text Value

Question

I have been using the REPLACE function to remove characters from a text value in a table. It has just grown over time and now the query is quite messy.

What can I do to make this more efficient? Is there a Regex example that I could implement?

Query:

SELECT
        REPLACE(
           REPLACE(
              REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(dbo.[RemoveNumericCharacters](Filename), '.xlsx', '')
                                        , '.xls', '')
                                        ,'csv', '')
                                        ,'---_pdf', '')
                                        ,'.', '')                                   
                                        ,'-Jun-', '')
                                        ,'-Jul-', '')
                                        ,'Jul', '')
                                        ,'__', '_')
           as 'ConvertedFilename'
FROM @Files 

Function:

ALTER Function [dbo].[RemoveNumericCharacters](@Temp nvarchar(1000))
Returns nvarchar(1000)
AS
Begin

    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

    Return @Temp
End

Answer 1

I think you'd better create a filter table in which you put your replacement string pairs and then you can create a function to remove source string with your filter table.

Here is the example

use tempdb
-- filter table
create table dbo.Filter (id int identity, src varchar(100), tgt varchar(100));
-- populate a few filtering pairs
insert into dbo.Filter (src, tgt) 
values ('.xlsx', '')
     , ('csv', '')
     , ('__', '_')
     , ('-Jun-', '');


-- your source table
create table #s (mycol varchar(1000))
-- populate source column
insert into #s (mycol) 
values ('this .xlsx file is not __ created in -Jun- but in June')
     , ('this csv file should be removed  -Jun-')
go

-- filtering function
create function dbo.funcReplace (@s varchar(1000))
returns varchar(1000)
as
begin
    declare @src varchar(100), @tgt varchar(100);
    declare @curS cursor;
    set @curS = cursor for select src, tgt from dbo.Filter
    open @curS
    fetch next from @curS into @src, @tgt; 
    while @@fetch_status = 0
    begin
        set @s=replace(@s, @src, @tgt);
            fetch next from @curS into @src, @tgt; 
    end
    return @s;
end
go

-- before replacement
select bfr_replacement = mycol from #s

-- after replacement
select  after_replacment=dbo.funcReplace(mycol)
from #s 

In future, if you have more replacement needed, just add them to the table dbo.Filter and you do not need to change anything elsewhere.

Answer 2

@jyao posted a good an elegant solution. But if you are not a big fun of using cursors in SQL Server... here you have an alternative solution just by spliting the text using a separator (Since you are working with file_name strings a good separator could be ":")... It is not elegant but I would test both solutions and see which one is faster for big amount of data. Unfortunately I have no access to install SQL Server in my pc so I did everything in Oracle and then translated the code to MsSQL... If you find any syntax issue just fix it properly.

CREATE FUNCTION [dbo].[ReplaceUndesiredText](@EvaluatedText nvarchar(1000))
Returns nvarchar(1000)
AS
Begin
  Declare 
        @old_list varchar(500)='__:.xlsx:.xls:csv:---_pdf:.:-Jun-:-Jul-:',
        @new_list varchar(500)='_::::::::',
        @separator char(1)=':'
        @old_val varchar(50),
        @new_val varchar(50),
        @final_pos_old int=1,
        @found_pos_old int=1,
        @begin_pos_old int,
        @final_pos_new int=1,
        @found_pos_new int=1,
        @begin_pos_new int

   While @found_pos_old > 0
     BEGIN
        select @begin_pos_old = @final_pos_old,
               @begin_pos_new = @final_pos_new
        select @found_pos_old = ISNULL(CHARINDEX(@separator,@old_list,@begin_pos_old),0),
               @found_pos_new = ISNULL(CHARINDEX(@separator,@new_list,@begin_pos_new),0)
        if (@found_pos_old>0) then
          select @old_val = SUBSTRING(@old_list,@begin_pos_old,@found_pos_old - @begin_pos_old),
                 @new_val = SUBSTRING(@new_list,@begin_pos_new,@found_pos_new - @begin_pos_new)
          set @evaluated_text = REPLACE(@evaluated_text,@old_val,@new_val)
        end if
        select @final_pos_new = @found_pos_new + 1,  
               @final_pos_old = @found_pos_old + 1  
     END
    Return @EvaluatedText
End