Find and replace string in main string

Question

I'm trying to find a string in a main string and remove it.

Example:

@Main_text = 'some text some text... // remove text remove text \\ some text some text'

What I want is to remove the following text:

// remove string remove string \\ of the main text

What I tried

declare @main_text varchar(255) = 'some text some text... // remove text remove text \\ some text some text'

SELECT STUFF(@main_text, 
         charindex('//', @main_text), 
         charindex('\\', @main_text) , 
   '');

This partly works. it removes the searched text but also remove the end of the text.

Tim Biegeleisen · Accepted Answer · 2018-04-05 10:17:00Z

4

The third parameter to STUFF is the number of characters, from the starting point in the second parameter, to replace.

SELECT
    STUFF(@main_text, 
        CHARINDEX('//', @main_text), 
        CHARINDEX('\\', @main_text) - charindex('//', @main_text) + 2, 
        '')
FROM yourTable
WHERE @main_text LIKE '%//%\\%'b

Demo

 text // blah \\
      6       14

We want to remove blah and remove the markers. The difference in marker positions returned from CHARINDEX is 8, but we want to remove 10 characters, hence the +2 in the STUFF call.

We may use a WHERE clause to restrict the query from only targeting records having the replacement markers. You could put the above query into a CTE, and then update it.

edited Apr 5, 2018 at 10:17

answered Apr 5, 2018 at 10:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tim Biegeleisen Over a year ago

@YogeshSharma I botched it...for some reason I thought CHARINDEX counted the end position of the string, rather than the beginning.

I_G Over a year ago

And if my main text does not contains the slash and backslash?

Tim Biegeleisen Over a year ago

@I_G Wait...I get it...just add a WHERE clause.

I_G Over a year ago

In my case I will use a "CASE". Thanks.

Collectives™ on Stack Overflow

Find and replace string in main string

1 Answer 1

Demo

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related