Reverse LIKE statement

[Psycho]

I have a table where I am storing records that include a file path. I need to determine if a new record to be added has a path which is a child directory of an existing record or a parent directory of an existing directory.

 

To check if the new path is a parent directory of an existing path is simple enough since an existing value will equal the new path with additional path info appended to the end:

SELECT *
FROM table
WHERE path LIKE '$new_path%'

 

However, checking to see if the new path is a sub directory of an existing path requires checking if any exisitng path is LIKE the new path. I came up with the following query which works, but I'm thinking there is probably an easier way that I am not aware of:

SELECT *
FROM table
WHERE '$new_path' LIKE concat(path, '%')

 

So, is there a built in function that will do this that I am not aware of?

[fenway]

Assuming you've accounted for the obvious boundary cases, you can just use %newpath%, right?

[Psycho]

Assuming you've accounted for the obvious boundary cases, you can just use %newpath%, right?

 

Not sure I follow you. new path will contain the complete path from the root of a drive, so using % at the beginning doesn't make sense to me. Using % at the end will only find other entries that are paths to sub-folders, or childs, of the new path (which I do want tio find). I also need to find paths that are parent folders of new path. Here is an example.But,

 

The new path to be added is:

C:\folderB\folder1\apple\

 

If the following values are from the current entries, I would need to find the ones indicated with a [P] or [C] for a parent or child directory:

    C:\folderA\
[P] C:\folderB\
    C:\folderA\folder1\
[P] C:\folderB\folder1\
    C:\folderB\folder2\
[C] C:\folderB\folder1\apple\sub1
[C] C:\folderB\folder1\apple\sub2
    C:\folderB\folder1\banana\
    C:\folderB\folder1\pear\

 

Using the first query I posted I can find the child directories. I can use the second query I posted to get the parent directories, but it just didn't seem an efficient manner.

[fenway]

Now I don't follow... why not use substring_index() to find the "root folder" and then simply check that against the left-most part of the path field?