Extract directory from Files field in SQLite table

Question

I have a SQLite table with a field named FullFilename. Now, I want to select the rows of files in a certain folder. Let's say the table contains:

c:\folder\file1.jpg
c:\folder\file2.jpg
c:\folder\subfolder\file1.jpg
c:\anotherfolder\file1.jpg

And I want to select only the rows with files in "c:\folder\". Not in a subfolder "c:\folder\subfolder\". This:

SELECT FullFilename FROM Files WHERE fullfilename LIKE "c:\folder\%"

won't work, because it also gives me the files in the subfolder.

How do I extract rows of files in a certain folder without getting files in a subfolders?

Edit

Because the answer is a bit buried in a comment below: Here is the correct answer that Mark provided:

SELECT FullFilename FROM Files 
WHERE fullfilename LIKE 'c:\folder\%' AND
      fullfilename NOT LIKE 'c:\folder\%\%'

I removed the lower() function as according to the SQLite documentation the LIKE operator is already case insensitive.

Answer 1

Use % instead of *. For the LIKE operator, % and _ are the wildcard counterparts to * and ?, respectively, as used in shell globbing.

Answer 2

SQL syntax uses % instead of * as the wild card in your LIKE clause.

Answer 3

Unless the syntax is different in SQLLite to most other forms of SQL, you should change the double quote characters to single quotes. Having done so, the query should return all records where the fullfilename begins with the exact string c:\folder\ - you may want to amend the query to be:

SELECT FullFilename FROM Files WHERE lower(fullfilename) LIKE 'c:\folder\%'

unless fullfilename values are always stored in lowercase; Windows file names are not case sensitive.

Note that this query will return all files in this folder and all subfolders.

I personally would recommend using separate fields for filename and filepath - it makes it easier to query.