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
