Replace pattern for individual characters

Question

I need to run a select statement that replaces individual characters in an varchar column. Each character in the column will be changed. A-Z need to change to 'A' and 0-9 need to change to '0'. I think I understand how to find them with regex, but I am not sure if the replace function can be used to do this. Examples

ABC123 > AAA000
Z15X97 > A00A00

Can this be done in tsql without using substring to change each character one at a time, or without a list (25 for letters, 8 for numbers) of statements like replace(<column>, 'B','A')?

Answer 1

Here is one way, its inefficient but hopefully this is only for a one-off

CREATE TABLE tbl (id INT, txt VARCHAR(255));
INSERT tbl VALUES (1, 'ABC123'), (2, 'Z15X97'), (3, 'AZ09'), (4, '0123456789ABCXYZ')

;WITH T (id, txt, pos) AS (
    SELECT id, CAST(txt AS VARCHAR(255)), 0 FROM tbl
    UNION ALL
    SELECT id,
        CAST(STUFF(txt, pos + 1, 1, CASE WHEN SUBSTRING(txt, pos + 1, 1) LIKE '[A-Z]' THEN 'A' ELSE '0' END) AS VARCHAR(255)),
        pos + 1
    FROM T WHERE pos < LEN(txt)
)
SELECT id, txt
FROM T WHERE pos = LEN(txt)

---

id  txt
4   0000000000AAAAAA
3   AA00
2   A00A00
1   AAA000

Answer 2

Try it like this:

DECLARE @tbl TABLE(TestString VARCHAR(100));
INSERT INTO @tbl VALUES('ABC123'),('Z15X97');

WITH RunningNummbers AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Inx
    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(y)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS a(b)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS c(d) --1000 running numbers
)
SELECT TestString
      ,(    
        SELECT CASE WHEN SUBSTRING(tbl.TestString,Inx,1) BETWEEN 'A' AND 'Z' THEN 'A' 
                    ELSE CASE WHEN SUBSTRING(tbl.TestString,Inx,1) BETWEEN '0' AND '9' THEN '0' ELSE SUBSTRING(tbl.TestString,Inx,1) END
               END
        FROM RunningNummbers
        WHERE Inx<=LEN(tbl.TestString)
        FOR XML PATH('')
        ) AS Replaced
FROM @tbl AS tbl

The result

ABC123  AAA000
Z15X97  A00A00