On May 3, 2014, at 5:39 PM, Simon Slavin wrote:
>
> On 3 May 2014, at 3:47pm, Petite Abeille wrote:
>
>> Let further assume one would like to use only SQLite's build-in mechanism
>
> There are two kinds of programmers …
Indeed: drunk and not yet drunk :D
Turns out that this group_concat scalar was really not needed at all. D'oh.
So, all-in-one now:
with
DataSet
as
(
select '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path
),
Component( path, leftover, component, component_path, position )
as
(
select path,
substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover,
substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component,
'/' || substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as
component_path,
1 as position
fromDataSet
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.component_path || '/' || substr( Component.leftover, 2,
instr( substr( Component.leftover, 2 ), '/' ) - 1 ) as component_path,
Component.position + 1 as position
fromComponent
where Component.leftover != '/'
)
selectposition,
component,
component_path
from Component
order by path, position;
position|component|component_path
1|subversion|/subversion
2|bindings|/subversion/bindings
3|swig|/subversion/bindings/swig
4|java|/subversion/bindings/swig/java
5|org|/subversion/bindings/swig/java/org
6|tigris|/subversion/bindings/swig/java/org/tigris
7|subversion|/subversion/bindings/swig/java/org/tigris/subversion
8|client|/subversion/bindings/swig/java/org/tigris/subversion/client
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users