replace all alphanumeric characters tsql

Question

I need to replace all alphanumeric characters with in the input with 'x'.

'12 34 - a'

becomes 'xx xx - x'. I tried to use

patindex

with [^a-zA-Z0-9], but after the first replacement still the same alphanumeric is found. looks that patindex works only when removing chars

can someone advice a solution for the issue

Answer 1

try this:

DECLARE @t VARCHAR(max) = '12 34 - a'

DECLARE @Keep VARCHAR(50)
SET @Keep = '%[a-vyz0-9]%'

WHILE PATINDEX(@Keep, @t) >0
    Set @t = Stuff(@t, PatIndex(@Keep, @t), 1, 'x')

    SELECT @t