In table data directories and files are stored as complete paths:
| rowid | path |
|---|---|
| 1 | c:/data/ |
| 2 | c:/data/test.txt |
| 3 | c:/data/sub1/ |
| 4 | c:/data/sub1/test.dat |
To get all items of a folder (directories and files) without the content of the sub-directories my approach failed. It returns content of sub-directories, too:
SELECT rowid FROM data WHERE path LIKE 'c:/data/'%;
result: 1, 2, 3, 4
expected result: 1, 2, 3
I then tried to check if second part of the string contains no "/" for files, or one "/" for sub-directories, failed.