====== REPLACE <pattern> ======
TSQL's ''REPLACE'' function is great for replacing literal values, but falls down when you want to replace //patterns// --- e.g. "remove all non-alphanumeric characters" (i.e. replace non-alphanumerics with an empty string).

This query uses a [[tsql:numbers_table|numbers table]] to split incoming strings into characters and pattern-match each individually, then uses ''[[tsql:string_concatentation_as_an_aggregate_operator|FOR XML PATH]]'' to stitch the results back together.

<code tsql>DECLARE @pattern VARCHAR(255) = '[^a-z0-9]'  -- = non-alphanumerics
DECLARE @replacement VARCHAR(255) = '' 

;WITH cte AS (
  SELECT
    my.MyTablePk
  , n.N
  , my.SomeValue
  --, SUBSTRING(my.SomeValue, n.N, 1)
  , CASE 
      WHEN SUBSTRING(my.SomeValue, n.N, 1) LIKE @pattern THEN @replacement 
      ELSE SUBSTRING(my.SomeValue, n.N, 1)
    END AS Replaced
  FROM dbo.MyTable my
    CROSS JOIN dbo.Numbers n
  WHERE n.N <= LEN(my.SomeValue)
)
SELECT
  MyTablePk
, SomeValue
, (
    SELECT Replaced
    FROM cte sub
    WHERE sub.MyTablePk = cte.MyTablePk
    ORDER BY N
    FOR XML PATH(''), TYPE
  ).value('.','VARCHAR(MAX)')
FROM cte
GROUP BY 
  MyTablePk
, SomeValue
</code>

{{tag> }}