Fork of
Untitled query #77227
by GreenC
This query is marked as a draft
This query has been published
by Cryptic.
SQL
AخA
WITH RECURSIVE numbers AS (SELECT 1 AS n UNION SELECT n + 1 FROM numbers WHERE n < 10),
mainquery AS
(
SELECT page_title,
page_namespace,
el_to_domain_index,
el_to_path,
el_id
FROM externallinks
JOIN page ON page_id = el_from
WHERE el_to_domain_index LIKE 'https://nl.kb.%' OR el_to_domain_index LIKE 'http://nl.kb.%'
)
SELECT page_title, page_namespace,
CONCAT(SUBSTRING_INDEX(el_to_domain_index, '://', 1),
'://',
SUBSTRING(GROUP_CONCAT(part ORDER BY n DESC SEPARATOR '.'), 2),
el_to_path) AS url
FROM
(
SELECT n, mainquery.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(el_to_domain_index, '://', -1), '.', n), '.', -1) AS part
FROM numbers
JOIN mainquery
ON CHAR_LENGTH(el_to_domain_index) - CHAR_LENGTH(REPLACE(el_to_domain_index, '.', '')) >= n - 1
) AS sq
GROUP BY el_id;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.