Given a path, say: /subversion/bindings/swig/java/org/tigris/subversion/client/
One would like to decompose it into all its components, say: /subversion/ /subversion/bindings/ /subversion/bindings/swig/ /subversion/bindings/swig/java/ /subversion/bindings/swig/java/org/ /subversion/bindings/swig/java/org/tigris/ /subversion/bindings/swig/java/org/tigris/subversion/ /subversion/bindings/swig/java/org/tigris/subversion/client/ Let further assume one would like to use only SQLite's build-in mechanism to achieve this. Any clever way to achieve this? FWIW, here is a rather weak attempt, using recursive CTE: with DataSet as ( select '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path ), Component( path, leftover, component, position ) as ( select path, substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover, substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component, 1 as position from DataSet union all select Component.path as path, substr( Component.leftover, instr( substr( Component.leftover, 2 ), '/' ) + 1 ) as leftover, substr( Component.leftover, 2, instr( substr( Component.leftover, 2 ), '/' ) - 1 ) as component, Component.position + 1 as position from Component where Component.leftover != '/' ) select path, component, position, ( select '/' || group_concat( self.component, '/' ) || '/' from Component self where self.path = Component.path and self.position <= Component.position group by self.path ) as component_path from Component order by path, position; While this work for one path, it doesn’t quite scale to multiple of them as the CTE gets re-executed over and over. One could cache the CTE in a temp table, and decompose the query into separated steps, but that would be rather inconvenient altogether. Thoughts? Suggestions? Alternatives? Thanks. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users