Replace a recurring word and the character before it

I am using SQL Server trying to replace each recurring “[BACKSPACE]” in a string and the character that came before the word [BACKSPACE] to mimic what a backspace would do.

Here is my current string: "This is a string that I would like to d[BACKSPACE]correct and see if I could make it %[BACKSPACE] cleaner by removing the word and $[BACKSPACE] character before the backspace."

Here is what I want it to say: "This is a string that I would like to correct and see if I could make it cleaner by removing the word and character before the backspace."

Let me make this clearer. In the above example string, the $ and % signs were just used as examples of characters that would need to be removed since they are before the [BACKSPACE] word that I want to replace.

Here is another before example: The dog likq[BACKSPACE]es it's owner

I want to edit it to read: The dog likes it's owner

One last before example is: I am frequesn[BACKSPACE][BACKSPACE]nlt[BACKSPACE][BACKSPACE]tly surprised

I want to edit it to read: I am frequently surprised

Answer

Without a CLR function that provides Regex replacement the only way you’ll be able to do this is with iteration in T-SQL. Note, however, that the below solution does not give you the results you ask for, but does the logic you ask. You state that you want to remove the string and the character before, but in 2 of your scenarios that isn’t true. For the last 2 strings you remove ' %[BACKSPACE]' and ' $[BACKSPACE]' respectively (notice the leading whitespace).

This leading whitespace is left in this solution. I am not entertaining fixing that, as the real solution is don’t use T-SQL for this, use something that supports Regex.

I also assume this string is coming from a column in a table, and said table has multiple rows (with a distinct value for the string on each).

Anyway, the solution:

WITH rCTE AS(
    SELECT V.YourColumn,
           STUFF(V.YourColumn,CHARINDEX('[BACKSPACE]',V.YourColumn)-1,LEN('[BACKSPACE]')+1,'') AS ReplacedColumn,
           1 AS Iteration
    FROM (VALUES('"This is a string that I would like to d[BACKSPACE]correct and see if I could make it %[BACKSPACE] cleaner by removing the word and $[BACKSPACE] character before the backspace."'))V(YourColumn)
    UNION ALL
    SELECT r.YourColumn,
           STUFF(r.ReplacedColumn,CHARINDEX('[BACKSPACE]',r.ReplacedColumn)-1,LEN('[BACKSPACE]')+1,''),
           r.Iteration + 1
    FROM rCTE r
    WHERE CHARINDEX('[BACKSPACE]',r.ReplacedColumn) > 0)
SELECT TOP (1) WITH TIES
       r.YourColumn,
       r.ReplacedColumn
FROM rCTE r
ORDER BY ROW_NUMBER() OVER (PARTITION BY r.YourColumn ORDER BY r.Iteration DESC);

dB<>fiddle