Geek asks and answers

Search subfolders of file path from file path table using SQL "LIKE" function

The column in one of my SQL Server database tables contains paths (not paths to files, just path to directory).

Currently, I have a query that returns all subfolders of a given file path using a function LIKE;

WHERE Path LIKE @FilePath + '%'

On the side of the note, I know there is a problem with this approach, since the escape characters used by LIKE are valid file path characters. I am going to use ESCAPE correctly in the finished request.

In any case, back to my question. I would like to be able to modify the request so that it returns only direct subfolders of the specified path. It's not as simple as putting a trailing slash at the end of a line, since it is obvious that the wildcard% matches everything, as it parses the right side of the line.

I was looking for using the escape sequence [^] to possibly do a search that excludes the \ character, but I had no luck with that. I have tried this;

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

But it does not give the expected result, so I'm not even sure if I use it correctly.

+5
source share
2 answers

LIKE . LEFT SUBSTRING , , .

, , C:\Temp \, , .

, , ('\') , .

, !

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
+2

LIKE:

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

( , "Path like @FilePath + '%'" ).

, @FilePath ( - ).

LIKE, :

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

More articles:


All Articles