3

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
3

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) > 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
  • If the filename is like this then it won't work... Numara_03_09_2018_07_41_04_OP_NIP_CSA_5739038.XLS – Kevin Sep 10 '18 at 7:56
  • @Kevin - Using the example in your comment, what should the result be? – Scott Hodgin Sep 10 '18 at 8:44
  • This...Numara_%_%_%_%_%_%_OP_NIP_CSA_%XLS - where groups of numbers are replaced with single % – Kevin Sep 10 '18 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.