none
T-SQL Replace at End of String

    Question

  • Hi

    Given a String 'This is a test, this is a second test'

    Is there an easy method (preferably without using CLR) to search and replace the string to remove the word 'test' but only if it occurs as the last series of characters in a string?

    The end result would be

    'This is a test, this is a second'

    Thanks!

    Josh

    Wednesday, November 03, 2010 11:02 PM

Answers

  • Given a String 'This is a test, this is a second test'

    Is there an easy method (preferably without using CLR) to search and replace the string to remove the word 'test' but only if it occurs as the last series of characters in a string?

    The end result would be
    'This is a test, this is a second'

    UPDATE tbl
    SET    str = substring(str, 1, len(str) - len(@badword))
    WHERE  str LIKE '%' + @badword

    If you want to consider trailing blanks, that is you want 'This is a test, and this is a second test ' to become 'This is a test, and this is a second', use rtrim:

    UPDATE tbl
    SET    str = substring(rtrim(str), 1, len(rtrim(str)) - len(@badword))
    WHERE  rtrim(str) LIKE '%' + @badword

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Proposed as answer by Naomi NModerator Thursday, November 04, 2010 12:01 AM
    • Marked as answer by Josh Ashwood Thursday, November 04, 2010 12:07 AM
    Wednesday, November 03, 2010 11:18 PM
  • > Is there a better way of achieving the above where the string is not being
    set in an update statement and a WHERE predicate can't be used?

    You did not say what the context was, so I assumed that it was in a UPDATE
    statement.

    You could do:

    IF @teststr LIKE '%' + 'test'
       SET @teststr = substring(@teststr, 1, len(@teststr) - len('test'))

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Josh Ashwood Thursday, November 04, 2010 10:11 PM
    Thursday, November 04, 2010 8:57 AM

All replies

  • Given a String 'This is a test, this is a second test'

    Is there an easy method (preferably without using CLR) to search and replace the string to remove the word 'test' but only if it occurs as the last series of characters in a string?

    The end result would be
    'This is a test, this is a second'

    UPDATE tbl
    SET    str = substring(str, 1, len(str) - len(@badword))
    WHERE  str LIKE '%' + @badword

    If you want to consider trailing blanks, that is you want 'This is a test, and this is a second test ' to become 'This is a test, and this is a second', use rtrim:

    UPDATE tbl
    SET    str = substring(rtrim(str), 1, len(rtrim(str)) - len(@badword))
    WHERE  rtrim(str) LIKE '%' + @badword

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Proposed as answer by Naomi NModerator Thursday, November 04, 2010 12:01 AM
    • Marked as answer by Josh Ashwood Thursday, November 04, 2010 12:07 AM
    Wednesday, November 03, 2010 11:18 PM
  • You could try something like...

    DECLARE @FindMe VARCHAR(100)
    DECLARE @SearchMe VARCHAR(100)
    SELECT @FindMe = 'test'
    SELECT @SearchMe = 'This is a test, this is a second test'

    SELECT
     SUBSTRING
      (
      @SearchMe,
      1,
      CASE CHARINDEX(REVERSE(@FindMe),REVERSE(@SearchMe))
      when 0 then 0   
      else LEN(@SearchMe) - ( CHARINDEX(REVERSE(@FindMe), REVERSE(@SearchMe)) + (LEN(@FindMe) - 1) ) + 1
      END - 1
      )
     +
     REPLACE
     (
      SUBSTRING
      (
      @SearchMe,
      CASE CHARINDEX(REVERSE(@FindMe),REVERSE(@SearchMe))
      when 0 then 0   
      else LEN(@SearchMe) - ( CHARINDEX(REVERSE(@FindMe), REVERSE(@SearchMe)) + (LEN(@FindMe) - 1) ) + 1
      END
      ,
      LEN(@SearchMe) - CASE CHARINDEX(REVERSE(@FindMe),REVERSE(@SearchMe))
      when 0 then 0   
      else LEN(@SearchMe) - ( CHARINDEX(REVERSE(@FindMe), REVERSE(@SearchMe)) + (LEN(@FindMe) - 1) ) + 1
      END + 1
      ),
      @FindMe,
      ''
     )

    This should still work even if you have something like: 'This is a test, this is a second test xxx', i.e. the xxx should still appear, of course it can be greatly simplified if 'test' is always on the end (can use RIGHT) but I assume this is not the case.

     

    Thanks


    Neil Moorthy Senior SQL Server DBA/Developer
    Wednesday, November 03, 2010 11:45 PM
  • Thanks very much for your answer Erland
    DECLARE @teststr VARCHAR(200);
    SET @teststr = 'This is a test, and this is a second test';
    
    SET @teststr = 
    			CASE WHEN @teststr LIKE '%' + 'test'
    		     THEN substring(@teststr, 1, len(@teststr) - len('test'))
    			ELSE @teststr
    			END;
    			
    			
    SELECT @teststr;
    
    Is there a better way of achieving the above where the string is not being set in an update statement and a WHERE predicate can't be used?
    Wednesday, November 03, 2010 11:46 PM
  • Hi Josh, you can use the REVERSE function to do it,  like this :

    DECLARE @teststr VARCHAR(200);
    DECLARE @test VARCHAR(200);
    SET @teststr = 'This is a test, and this is a second test';
    SET @TEST='test'
    select reverse(substring(reverse(@teststr),CASE WHEN CHARINDEX(reverse(@test),reverse(@teststr))<>1 THEN 1 ELSE len(@test)+CHARINDEX(reverse(@test),reverse(@teststr)) END ,CASE WHEN CHARINDEX(reverse(@test),reverse(@teststr))<>1 THEN len(@teststr) ELSE len(@teststr)-len(@test) END))
    

    you can use your update without 'WHERE'

     

     

     


    Best regards
    Thursday, November 04, 2010 8:48 AM
  • > Is there a better way of achieving the above where the string is not being
    set in an update statement and a WHERE predicate can't be used?

    You did not say what the context was, so I assumed that it was in a UPDATE
    statement.

    You could do:

    IF @teststr LIKE '%' + 'test'
       SET @teststr = substring(@teststr, 1, len(@teststr) - len('test'))

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Josh Ashwood Thursday, November 04, 2010 10:11 PM
    Thursday, November 04, 2010 8:57 AM
  • Awesome, thanks Erland!
    Thursday, November 04, 2010 10:11 PM