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_str looking_for instance_no instance_start
---------------------- ----------- ----------- --------------
Here kitty kitty kitty kitty 2 12
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_str looking_for instance_no instance_start
---------------------- ----------- ----------- --------------
Here kitty kitty kitty kitty 4 0
-----Original Message-----
From: sqlite-users [mailto:[email protected]] 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
