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?
-- 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 +'%'