externallinks query w/readable urls wo/regexp replace

Example query of how to turn externallinks' new fields into normal urls.  There's a better method in query 77235.

For [[WP:RAQ#external links -- el_to]] circa 12 October 2023.

SQL

WITH RECURSIVE numbers AS (SELECT 1 AS n UNION SELECT n + 1 FROM numbers WHERE n < 10),
mainquery AS
(
  SELECT page_title,
         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://fr.lemonde.%' OR el_to_domain_index LIKE 'http://fr.lemonde.%'
  AND page_namespace = 0
)
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;

SQL

Checking query status...