externallinks query w/readable urls w/regexp replace

Fork of externallinks query w/readable urls w/regexp replace by GreenC

This query is marked as a draft This query has been published by Geagea.

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 12 October 2023.

SQL

SELECT page_title,
       page_namespace,
       CONCAT(REGEXP_REPLACE(el_to_domain_index,
                             '^(.*?://)(?:([^.]+)\\.)([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)$',
                             '\\1\\9\\8\\7\\6\\5\\4\\3\\2'),
              el_to_path) AS url
FROM externallinks
JOIN page ON page_id = el_from
WHERE el_to_domain_index LIKE 'https://merhav.nli.org.il/primo-explore/fulldisplay?docid=ULI_Main003775956&context=L&vid=ULI&lang=iw_IL&utm_source=he.wikipedia.org&utm_medium=referral&utm_campaign=%22%D7%91%D7%90%D7%92%D7%A1%D7%99+%D7%A1%D7%99%D7%92%D7%9C%22&utm_content=ULI%';

SQL

Checking query status...