How to limit STUFF function within CTE for removing character before recurring word

A follow up question for Replace a recurring word and the character before it

Credit to @Larnu for the following code, how would this code be modified to not produce a null value if there is no longer a character before a recurring word, “[BACKSPACE]”. I believe the null value is occurring because the CHARINDEX() functions are producing a negative value when there is no longer a character before the recurring [BACKSPACE] word. The solution would need to be compatible with SQL Server 2008.

The ultimate goal is for anywhere that there is a [BACKSPACE], IF there is a character before [BACKSPACE], remove it; otherwise, don’t try to remove the character before.

DECLARE @inputString NVARCHAR(MAX);

SET @inputString = 'Word[BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE]sgred[BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE]SecondWord';

WITH rCTE AS(
    SELECT V.YourColumn,
           STUFF(V.YourColumn,CHARINDEX('[BACKSPACE]',V.YourColumn)-1,LEN('[BACKSPACE]')+1,'') AS ReplacedColumn,
           1 AS Iteration
    FROM (VALUES(@inputString))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);

The desired output is 'SecondWord'

Answer

You need a CASE check to see if you are at the beginning of the string, in which case you don’t want the position before, as that is 0.

Some other improvements I would make to this code: LEN could be calculated once, and we could store the [BACKSPACE] value in a variable also

DECLARE @inputString NVARCHAR(MAX);

SET @inputString = 'Word[BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE]sgred[BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE][BACKSPACE]SecondWord';

DECLARE @bspace nvarchar(50) = '[BACKSPACE]';
DECLARE @len int = LEN(@bspace);

WITH rCTE AS(
    SELECT V.YourColumn,
           STUFF(V.YourColumn,CHARINDEX(@bspace,V.YourColumn)-1,@len+1,'') AS ReplacedColumn,
           1 AS Iteration
    FROM (VALUES(@inputString))V(YourColumn)
    UNION ALL
    SELECT r.YourColumn,
           CASE WHEN CHARINDEX(@bspace,r.ReplacedColumn) = 1
             THEN STUFF(r.ReplacedColumn,1,@len,'')
             ELSE STUFF(r.ReplacedColumn,CHARINDEX(@bspace,r.ReplacedColumn)-1,@len+1,'')
           END,
           r.Iteration + 1
    FROM rCTE r
    WHERE CHARINDEX(@bspace,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);