Masking part of a filename with a certain pattern

  • Hi there

    I have the following input string which represents the name of a file I have created


    There are 7 files named in the target directory


    Now this is time date stamped and could be 7_Of_7 of  3_of_3 etc


    Now I want to be able to identify the part in the string   '1_of_7' and replace this wih '*_Of_*'

    So I would now have a search string


    And then I can use this to search for all files matching the above pattern

    I tried using something like

    dECLARE @FilePath VARCHAR(100) = 'SVPP_ROYA_00001_00021_1_of_7_20200326_1011161.csv'

    DECLARE @FindChar VARCHAR(1) = '%*_of_*%'

    select patindex(@FilePath, @FindChar)

    select CHARINDEX(@FilePath, @FindChar)

    But cant locate this.

    How can identify this part of the string to find my 7 files?


    Could I used something like Replace('SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv','%_#of_#_%','_*_of_*_'?


  • I used the following code to achieve the expected result.

    DECLARE @FileName nvarchar (256) = N'SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv';

    DECLARE @PatIndex nvarchar (32) = N'%[0-9][_]of[_][0-9]%';

    SUBSTRING(@FileName, 0, PATINDEX(@PatIndex, @FileName)),
    REVERSE(SUBSTRING(REVERSE(@FileName), 0, PATINDEX(REPLACE(@PatIndex, 'of', 'fo'), REVERSE(@FileName))))

    The last part of the concatenation is the tricky part and I'm really curious if there is a better way to solve for this. I have to reverse the inner "of" to "fo" to look against the string in reverse to identify where the pattern ends. Then I reverse the result of that reversed substring to append it to it's original name. I could see this breaking if the file number exceeded 1 digit, but the @PatIndex variable could be adjusted accordingly. I hope this helps!

    As I mentioned, I think there are several ways to solve for this, hoping to see some more solutions here!

  • I'm sure one of our resident geniuses can simplify this, but I think this responds to your requirement.

    Depends on x_of_y, where x is the 5th element and y is the 7th in the filename between underscores.


    WITH tStrings AS (SELECT FileName = 'SVPP_ROYA_00001_00021_6_of_9_20200326_101116.csv'
    UNION SELECT 'SVPP_ROYA_00001_00021_7_of_9_20200326_101116.csv'
    UNION SELECT 'SVPP_ROYA_00001_00021_1_of_2_20200326_101115.csv'
    UNION SELECT 'SVPP_ROYA_00001_00021_2_of_2_20200326_101115.csv'
    UNION SELECT 'SVPP_ROYA_00001_00021_11_of_12_20200324_101115.csv'
    UNION SELECT 'SVPP_ROYA_00001_00021_12_of_12_20200324_101115.csv'),
    FROM tStrings t
    CROSS APPLY dbo.ufn_DelimitedStringToTable (FileName, '_')),
    t2 AS (SELECT DISTINCT tSearch = STUFF((SELECT '_' + CASE WHEN tmp1.nRow IN (5, 7) THEN '%' ELSE tmp1.cRow END
    FROM t1 AS tmp1
    WHERE tmp1.FileName = tmp2.FileName
    ORDER BY tmp1.FileName, tmp1.nRow
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, '')
    FROM t1 AS tmp2)

    SELECT tSearch, FileName
    FROM tStrings
    JOIN t2 ON PATINDEX(tSearch, FileName) > 0
  • Thanks all

  • Sorry, just noticed that my solution depends on my function dbo.ufn_DelimitedStringToTable.

    You can find excellent string splitters on this site with a quick search.





Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply