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 numbers table to split incoming strings into characters and pattern-match each individually, then uses FOR XML PATH to stitch the results back together.

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