Thanks for that, will study it.
I have been fiddling and found a reasonable solution. It has to do with the
CTE to display a folder structure as posted before:
with folderpath(id, rightmost_folder_id, parent_id, path, rank, cnt) as
(select id, id, parent_id, name, rank, 0 from folders
union all
select f.id, fp.rightmost_folder_id, f.parent_id, f.name || '/' || fp.path,
fp.rank, fp.cnt + 1 from folders f inner join folderpath fp on(f.id ==
fp.parent_id))
select rightmost_folder_id as folder_id,
substr('___', 1, length(rtrim(path,
replace(path, '/', '' ||
substr('//', 1, cnt) ||
replace(path, rtrim(path, replace(path, '/', '' )), '') as path
from folderpath
where id == 1
order by rank
Not sure there is a better way to handle the substr bits, something like
char(x, y) meaning y repetitions of char x.
RBS
On Tue, May 14, 2019 at 9:16 PM David Raymond
wrote:
> Well, one way is that you can make a sub-query that uses a recursive CTE
> to find it. I've got something that appears to work, but it seems overly
> convoluted, so I'm going to feel embarrassed when someone posts a simple
> elegant version in a couple minutes.
>
>
> Say for example you have
>
> create table stuff (full_str text);
> insert into stuff values ('Here kitty kitty kitty');
>
> Then if you're looking for 'kitty' and want the start of instance #2 you
> could do this... (Using binding methods of your platform where appropriate)
>
>
> select full_str, 'kitty' as looking_for, 2 as instance_no,
> coalesce(
> (with recursive foo
> (instance_no, instance_start, remaining_str, remaining_str_pos)
> as (values (0, 0, stuff.full_str, 0)
> union all
> select instance_no + 1,
> remaining_str_pos + instr(remaining_str, 'kitty'),
> substr(remaining_str, instr(remaining_str, 'kitty') + length('kitty')),
> remaining_str_pos + instr(remaining_str, 'kitty') - 1 + length('kitty')
> from foo where instr(remaining_str, 'kitty'))
> select instance_start from foo where instance_no = 2),
> 0) as instance_start
> from stuff;
>
>
> sqlite> ...
> QUERY PLAN
> |--SCAN TABLE stuff
> `--CORRELATED SCALAR SUBQUERY 3
>|--CO-ROUTINE 2
>| |--SETUP
>| | `--SCAN CONSTANT ROW
>| `--RECURSIVE STEP
>| `--SCAN TABLE foo
>`--SCAN SUBQUERY 2
> full_strlooking_for instance_no instance_start
> -- --- --- --
> Here kitty kitty kitty kitty212
>
>
> Or if you're looking for an instance that's more than the number that
> there actually is, this returns 0. Or whatever value you want, just make it
> the second part of the coalesce.
>
> full_strlooking_for instance_no instance_start
> -------------- --- --- --
> Here kitty kitty kitty kitty40
>
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Bart Smissaert
> Sent: Tuesday, May 14, 2019 3:02 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Instr(x, y, z) ?
>
> Is it possible with the existing SQL core string functions to find the
> position of occurrence z of string y in string x?
> The standard Instr function only does this for the first occurrence, but I
> would like to specify the second, third, fourth etc. occurrence of the
> specified string.
> As this is with Android coding I can't make a UDF for this.
>
> Alternatively, I could try something similar to InstrRev (as in VB6),
> finding the last occurrence of string y in string x.
>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
