বাংলা উইকি:All pages with links starting with

Example query of how to turn externallinks' new fields into normal urls, relying on regexp_replace() instead of the more roundabout method in query 77231.

For [[WP:RAQ#external links -- el_to]] circa 19 December 2023.
Update the information on https://bn.wikipedia.org/s/7wcs

SQL

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 'http://org.banglapedia.en.%' OR el_to_domain_index LIKE 'https://org.banglapedia.en.%'
)
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
AND page_namespace=0
GROUP BY el_id;

SQL

Checking query status...