Replace Function With Recursive CTE

Last 2 posts we learned some cool ways to use the STUFF function. This post, we will combine the power of recursive common table expression (CTE), STUFF and PATINDEX function to write our own script to replace a string pattern. Some of you may ask why bother when SQL Sever already has the built-in REPLACE function? Well, this is just for fun and it doesn't hurt to practice writing sql. : )

/***********************************************************************
 Replace a pattern with another string pattern.
 This is almost the same as the built-in TSQL REPLACE function.
 Ex: remove all commas within a string. 
************************************************************************/

--
DECLARE @original_str VARCHAR(100) = 'The, quick, brown, fox, jumps, over, the, lazy, dog'
      , @pattern_str VARCHAR(100)  = ','
      , @replacement_str VARCHAR(100) = '';

 -- recursive CTE: replace @pattern_str with @replacement_str 
; WITH phrase(string, row_id) AS (
   -- anchor member 
   SELECT @original_str AS string, row_id = 1
   UNION ALL
   -- recursive member */
   -- Note: If start_position <= 0, STUFF function returns NULL 
   SELECT
      --Convert to VARCHAR(100) for data type and length to match with anchor member
      -- Case expression to handle NULL and string with no match found
      CAST(CASE WHEN PATINDEX('%'+@pattern_str+'%', string) <= 0 THEN string
             ELSE STUFF(string, PATINDEX('%'+@pattern_str+'%', string), 1,@replacement_str)
            END AS VARCHAR(100)
      ) AS string
      , row_id = row_id + 1
   FROM phrase
   WHERE PATINDEX('%'+@pattern_str+'%', string) > 0 -- stop when pattern is not found
)
SELECT string AS replaced_string
FROM phrase
ORDER BY row_id DESC
OFFSET 0 ROW FETCH NEXT 1 ROW ONLY -- get last output from recursive CTE above
OPTION(MAXRECURSION 0);

If you have a solution please share. I'm sure there're better and simpler solutions than this.
For me, it's fun to manipulate character strings. I will provide more examples on string manipulation on future posts. Also we'll learn more about CTE and recursive CTE.