0

I have a static table containing filenames (StaticFilesTbl).

Each day a file is sent in that is stored in DailyFilesTbl.

The filenames in DailyFilesTbl will be different from StaticFilesTbl in that they will contain a date (in any format and in any location) in the filename.

I want to return files from DailyFilesTbl that 'match' - using LIKE - to the files in StaticFilesTbl.

In my code below I try to do this by putting a '%' into the StaticFilename value after an underscore - which works. However, I then have an issue if it is needed after multiple underscores.

Is there a better way to do this?

http://sqlfiddle.com/#!18/a0adfe/1

-- Table that holds static file names
CREATE TABLE StaticFilesTbl
(
    StaticFilename nvarchar(150)
)
INSERT INTO StaticFilesTbl(StaticFilename)
VALUES('SG__123456'),
        ('TFF_x2__x3');


-- update static table to add '%' for searches
UPDATE StaticFilesTbl
SET StaticFilename = STUFF(StaticFilename, CHARINDEX('_', StaticFilename)+1, 0, '%');


-- table that holds files received each day (date is on filename and changes daily)
CREATE TABLE DailyFilesTbl
(
    DailyFilename nvarchar(150)
)
INSERT INTO DailyFilesTbl(DailyFilename)
VALUES('SG_20180731_123456')
        ,('SG_20180730_123456')
        ,('SG_20180730_x2')
        ,('TFF_x2_20180730_x3');


-- query daily table for files
SELECT * 
FROM DailyFilesTbl d
INNER JOIN
    StaticFilesTbl s
    ON d.DailyFilename LIKE s.StaticFilename +'%'
4

you need to remember that in a like statement a _ is ANY single character, and a % is any number (including 0) characters

on your insert for StaticFilesTbl if you use the search strings you wish for in there (and bracket out the underscores) that should i believe get you what you want

INSERT INTO #StaticFilesTbl(StaticFilename)
VALUES('SG[_]%[_]123456'),
        ('TFF[_]x2[_]%[_]x3');

Also you'll nolonger need the + '%' on the end of the LIKE statement as it has everything built in already

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.