-- Create test table.
CREATE TABLE Foobar (
key_col INT PRIMARY KEY,
text_col NVARCHAR(100));
-- Populate sample data.
INSERT INTO Foobar VALUES (1, N'ABC!@#%DEFgh');
INSERT INTO Foobar VALUES (2, N'~!102WXY&*()_Z');
-- Perform the cleanup with recursive CTE.
WITH Clean (key_col, text_col, ch)
AS
(SELECT key_col,
REPLACE(text_col, CHAR(255), ' '),
255
FROM Foobar
UNION ALL
SELECT key_col,
CASE WHEN
CHAR(ch - 1) NOT LIKE '[A-Z]'
THEN REPLACE(text_col, CHAR(ch - 1), ' ')
ELSE text_col END,
ch - 1
FROM Clean
WHERE ch > 1)
SELECT key_col, text_col
FROM Clean
WHERE ch = 1
OPTION (MAXRECURSION 255);
WITH Clean (key_col, text_col)
AS
(SELECT key_col, REPLACE(CAST(
(SELECT CASE
WHEN SUBSTRING(text_col, n, 1) LIKE '[A-Z]'
THEN SUBSTRING(text_col, n, 1)
ELSE '.'
END
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(text_col)
FOR XML PATH('')) AS NVARCHAR(100)), '.', ' ')
FROM Foobar)
SELECT key_col, text_col
FROM Clean;
Labels: common table expressions, recursive CTE, sql server, t-sql programming, xml