sql list of records within a path scope

Question

I have a master table containing URLs:

CREATE TABLE IF NOT EXISTS MasterTable (url, masterId, PRIMARY KEY(url), UNIQUE(masterId));

An url string looks like this: file:///Users/user1/Folder1/Folder2/.../FolderN/filename1.jpeg.

Now, I need to write a query that for a path example path = 'file:///Users/user1/Folder1/Folder2/Folder3' will return all masterIds of filenames that are in this exact folder path but not further.

I guess I have to use some combination of trim functions but can't figure it out myself.

Answer 1

Use LIKE and then count how many '/' are in the path to make sure you dont get deeper folders.

SELECT masterId
FROM MasterTable 
WHERE url like 'file:///Users/user1/Folder1/Folder2/Folder3/%'
  AND length(url) - length( replace(url, '/', '') ) = 8 

more generic:

 AND length(url) - length( replace(url, '/', '') ) = 
     length('file:///Users/user1/Folder1/Folder2/Folder3/') 
   - length( replace('file:///Users/user1/Folder1/Folder2/Folder3/', '/', '') )