1

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
  • More efficient? Could be a CLR function. – EzLo Jul 10 '18 at 14:34
  • All TSQL solutions tend to be complicated. This is preferably done in the font-end in a high-level programming language (other than SQL). If you still want to do it in TSQL: social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Olivier Jacot-Descombes Jul 10 '18 at 14:39
  • @EzLo is right about CLR. There's a very good example of it in the companion material for T-SQL Querying. – Erik Darling Jul 10 '18 at 14:42
  • Is it inefficient? Have you noticed performance issues with it as it gets more complex? Is that complexity worth introducing regular expressions? The answer may surprise you. – Randolph West Jul 10 '18 at 21:10
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 

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.

1

@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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.