Search code examples
sqlitejoinselectinner-joinsql-like

Using multiple search criteria in SQLite LIKE clause based on a nested SELECT

I have a list of full-paths in the "SOURCE" table in SQLite and a list of specific filenames (which will obviously be part of the path) in a SPECIFICFILES table.

I want to find the files in the SOURCE table based on their filenames. I'm trying to use "LIKE" to match part of the path, but the search below returns nothing:

select * from SOURCE WHERE FullPath like (select char(39)||'%'||"SPECIFICFILES"."Filename"||'%'||char(39) FROM SPECIFICFILES);

(Char(39) is a quote in ASCII).

This query returns nothing. Is this possible in SQLite please or do I need to use C# to fire off each query in turn?

Adding some sample data:

Table SOURCE:
FullPath
C:\My Directory\MyFile.txt

Table SPECIFICFILES:
Filename
MyFile.txt
Solution

  • You can do it by joining the tables:

    SELECT s.*, f.* 
FROM SOURCE s INNER JOIN SPECIFICFILES f
ON s.FullPath LIKE '%\' || f.Filename || '%';

    I'm not sure why you used in your code CHAR(39).
    Is it part of the paths?
    Also if the filename is at the end of FullPath, without any trailing \, there is no need to concatenate the wildcard % at the end:

    SELECT s.*, f.* 
FROM SOURCE s INNER JOIN SPECIFICFILES f
ON s.FullPath LIKE '%\' || f.Filename;

    See the demo.