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) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')
select @Temp
Best Answer
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.
Result