Sql-server – TSQL Remove Characters from Text Value

replacesql servert-sql

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

Best Answer

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

enter image description here

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.

Related Question