Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tsql:replace_pattern [2017/07/21 21:47] (current)
Line 1: Line 1:
 +====== 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> }}
 +~~DISCUSSION~~
 +