Sql-server – TSQL – Finding Similar Values

sql serverstring-searchingt-sql

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

Best Answer

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

Related Question