Finding subfolders of a file path from table of file paths using SQL Server "LIKE" function

Question

A column in one my SQL Server database tables contains paths (not file paths, just the directory path).

I currently have a query which returns all sub-folders of a given file path, using the LIKE function;

WHERE Path LIKE @FilePath + '%'

On a side note, I am aware that there is an issue with this approach as the escape characters used by LIKE are all valid file path characters. I'm going to make proper use of ESCAPE in the finished query.

Anyway, back to my question. I would like to be able to modify the query so that it returns only direct sub-folders of the specified path. This isn't as easy as just putting a trailing slash on the end of the string, as obviously the % wildcard gobbles everything up as it parses the right portion of the string.

I was looking at using the [^] escape sequence, to perhaps do a search which excludes the \ character, but I've had no luck with that. I tried this;

WHERE Path LIKE @FilePath + '[^\]'

But it doesn't produce the intended result, so I'm not even sure if I'm using it correctly.

Answer 1

Here is a way to do this without LIKE:

WHERE left(Path, len(@FilePath)) = @FilePath and
      charindex('\', substr(Path, len(@FilePath)+2, 1000)) = 0

(The first part of the statement is the same as "Path like @FilePath + '%'").

The second part just looks at the string and checks that there are no backslashes after the @FilePath (presumably the next character is a backslash).

If you prefer LIKE, you can write this as:

where Path like @FilePath + '%' and
      Path not like @FilePath + '%[\]%'

Answer 2

Do not use LIKE for this. LEFT and SUBSTRING will give better performance as you are not doing any wildcard searches, which you should try and avoid in all instances.

The query below will do this and exclude an exact match, so if there is a directory 'C:\Temp\ it will not show up in the results, but all the sub directories will.

What we are doing is throwing away all results that possibly have more sub directories ('\') and just using the ones with a single sub directory.

Hope this helps!

DECLARE @directory VARCHAR(150);
SET @directory = 'C:\Temp\';

SELECT 
    * 
FROM 
    tblDir
WHERE 
    LEFT(Dir, LEN(@directory)) = @directory
    AND CHARINDEX('\', SUBSTRING(Dir, LEN(@directory) ,LEN(Dir) - LEN(@directory)), 2) = 0
    AND Dir <> @directory