Klaus. The CTE manual with good examples is at https://www.sqlite.org/lang_with.html IgorT posted some good stuff about your problem using CTE.
FYI. TRIGGER is also recursive. Could be more efficient if you have to store them anyway: CREATE TABLE domain(d TEXT); CREATE TRIGGER domain_after_ins AFTER INSERT ON domain WHEN instr(NEW.d,'.') BEGIN INSERT INTO domain VALUES(substr(NEW.d,instr(NEW.d,'.')+1)); END; PRAGMA recursive_triggers=1; INSERT INTO domain VALUES ('a.b.c.d'); INSERT INTO domain VALUES('e.f.g.1.2'); SELECT * FROM domain; d ---------- a.b.c.d b.c.d c.d d e.f.g.1.2 f.g.1.2 g.1.2 1.2 2 Peter On Mon, Dec 11, 2017 at 9:22 AM, Klaus Maas <k...@maasser.eu> wrote: > Thank you, Igor, for your solution. > I expected that I would be pointed to a recursive approach. > Your solution is quite elegant in my opinion. > My problem, however, is that I have not yet understood recursive CTE's. > Could you give me some pointers for good resources to learn about them? > > Well, your code example is helping a lot in understanding them, although I > am still a long way off producing something similar myself without > assistance. > > There is 1 item in the last where condition of which I do not understand > the purpose: > (instr(subdomain, '.') = 0 and subdomain = long) > This means that anything in the form of 'ftp://test/' would output the > string between the two delimiters (:// and /), in this case 'test'. > But that is not a domain name in the format domain.tld. > (I am working under the assumption that table links is cleaned up contains > valid links only with the protocol and :// prepended.) > Or am I missing something?. > > Klaus > > ------------------------------------------------------------------------ > On 2017-12-11 14:59, Igor Tandetnik wrote: > >> Something like this (could likely be simplified further, but this should >> give the idea): >> >> with recursive subdomains as ( >> SELECT substr(link, instr(link, '://')+3, instr(substr(link, >> instr(link, '://')+3), '/')-1) AS long, >> substr(link, instr(link, '://')+3, instr(substr(link, >> instr(link, '://')+3), '/')-1) as subdomain >> FROM links >> union all >> select long, substr(subdomain, instr(subdomain, '.') + 1) >> from subdomains >> where instr(subdomain, '.') > 0) >> select * from subdomains >> where (instr(subdomain, '.') = 0 and subdomain = long) OR >> (instr(subdomain, '.') > 0 and instr(substr(subdomain, >> instr(subdomain, '.') + 1), '.')=0); >> >> The main point is to recursively build a table of all suffixes, then >> select just the suffixes you want. >> > > _______________________________________________ > 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