I'm storing pages for websites inside a 'pages' database table, they're recommended by their path (i.e. 'articles/my-first-blog-post'), I have to choose all of the kids of a specific page, but without choosing grandchildren.

Therefore if I run:

SELECT * FROM pages WHERE path LIKE 'articles%'

I'll get pages using the following pathways:

articles/one

articles/two

articles/two/more

articles/three

articles/three/more/even-more

I have to filter them (within the query) to simply:

articles/one

articles/two

articles/three

Can there be anyway to behave like:

SELECT * FROM pages WHERE path LIKE 'articles%' AND path NOT LIKE 'articles%/%'

Any ideas? Cheers.

In case your files have file extensions this works:

   SELECT * FROM pages 
    WHERE path LIKE 'articles%' 
      AND SUBSTRING_INDEX(path,'/',2) LIKE '%.file_extension';

otherwise:

   SELECT * FROM pages 
    WHERE path LIKE 'articles%' 
      AND SUBSTRING_INDEX(path,'/',2)=SUBSTRING_INDEX(path,'/',3)=;

You should use regular expressions for your. The keyword REGEXP works for both mysql and sqlite:

... WHERE path REGEXP '^articles/[^/]+'

Using regular expressions (LIKE clause or REGEXP) may incur severe performance problems in SQLite simply because they require full table scan. Find out more relating to this in http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html.

You should use inequality operators (like < and >) to improve performance (when the relevant column comes with an index).

Liron Levi (author of SQLite Compare diff/merge tool)

SELECT * FROM pages WHERE path LIKE "%/%" AND path NOT LIKE "%/%/%";

works best for me a minimum of.