Replace all Numeric Values with Single Character I have a value in a SQL table like the below:

80400365_SwapOpenTrd_20180831.csv

I want to replace the numeric values so it is returned as this:

%_SwapOpenTrd_%.csv

I tried the below code but there are groups of % characters returned...

%%%%%%%%_SwapOpenTrd_%%%%%%%%%csv

What do I need to change here to get desired result?

http://sqlfiddle.com/#!18/9eecb/33261 

DECLARE @Temp nvarchar(150) = '80400365_SwapOpenTrd_20180831.csv'

Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z_% ]%' -- keep underscore and space
While PatIndex(@KeepValues, @Temp) &gt; 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')

select @Temp
Based on your latest comment, I made few changes to your original example.  The following logic prevents repetitive % from being stuffed.  

See if this works for you.

DECLARE @Temp NVARCHAR(150) = 'Numara_03_09_2018_07_41_04_OP_NIP_CSA_5739038.XLS'
DECLARE @KeepValues AS VARCHAR(50)

SET @KeepValues = '%[^a-z_%. ]%' -- keep underscore and space

WHILE PatIndex(@KeepValues, @Temp) &gt; 0
BEGIN
    SET @Temp = CASE 
            WHEN substring(@temp, PatIndex(@KeepValues, @Temp) - 1, 1) = '%'
                THEN Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
            ELSE Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')
            END
END

SELECT @Temp




Result

Numara_%_%_%_%_%_%_OP_NIP_CSA_%.XLS