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';
SELECT * FROM pages WHERE path LIKE 'articles%' AND SUBSTRING_INDEX(path,'/',2)=SUBSTRING_INDEX(path,'/',3)=;
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.