Lets say I have a table T with a column called S

I want to remove every substring in each row of T.S that is between and and replace it with nothing such that Table T becomes

I have figured out how to identify the characters between the first instance of and

IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
BEGIN -- Checking to ensure temp table does not exist, and dropping anyone that does to avoid errors

    DROP TABLE #t;
END;

GO

--- Create Temp Table to store values and manipulate before inserting into production table ----

CREATE TABLE #t
(
Code VARCHAR(MAX)
,S VARCHAR(MAX)
);

INSERT INTO #t (Code
                 ,S
                )
VALUES ('1','String String'),
        ('2','Laser Laser'),
        ('3', 'Lasers and Strings')
SELECT *
FROM   #t;


SELECT REPLACE(REPLACE(SUBSTRING(s, start_pos,  end_pos - start_pos  
    +1), '', ''), '', '')

FROM   (SELECT s,
               CHARINDEX('', s) AS start_pos, 
               CHARINDEX('', s, CHARINDEX('', s) + 1) AS end_pos
        FROM   #t) t 

This returns

Where I am stuck is on

1. How do I get it to apply to all instances of in the same line individually (see line 3)

2. Apply this change to the column in an update (I’m thinking a cross apply might be useful here)