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. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users