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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
