Titles w/serial commas

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

Page titles that exist in mainspace and have a serial comma, whether they're redirects, and what the titles would be if the serial comma was removed.


For [[WP:RAQ#Serial commas in page titles]] circa 20 January 2025.

SQL

WITH p1 AS
(
  SELECT REPLACE(page_title, '_', ' ') AS title,
         page_is_redirect AS is_redirect,
         REGEXP_REPLACE(REPLACE(page_title, '_', ' '),
                        '(\\w, (?:\\w| )+),( (?:and|or) \\w)',
                        '\\1\\2') AS comma_removed
  FROM page
  WHERE page_namespace = 0
    AND REPLACE(page_title, '_', ' ') REGEXP '\\w, (?:\\w| )+, (?:and|or) \\w'
)
SELECT p1.*,
       CASE
         WHEN page_id IS NULL THEN 'doesn''t exist'
         WHEN page_is_redirect = 1 THEN 'redirect'
         ELSE 'non-redirect'
       END AS comma_removed_status
FROM p1
LEFT JOIN page ON page_namespace = 0 AND page_title = REPLACE(comma_removed, ' ', '_')
ORDER BY title ASC;

AخA
 
WITH p1 AS(  SELECT REPLACE(page_title, '_', ' ') AS title,         page_is_redirect AS is_redirect,         REGEXP_REPLACE(REPLACE(page_title, '_', ' '),                        '(\\w, (?:\\w| )+),( (?:and|or) \\w)',                        '\\1\\2') AS comma_removed  FROM page  WHERE page_namespace = 0    AND REPLACE(page_title, '_', ' ') REGEXP '\\w, (?:\\w| )+, (?:and|or) \\w')SELECT p1.*,       CASE         WHEN page_id IS NULL THEN 'doesn''t exist'         WHEN page_is_redirect = 1 THEN 'redirect'         ELSE 'non-redirect'       END AS comma_removed_statusFROM p1LEFT JOIN page ON page_namespace = 0 AND page_title = REPLACE(comma_removed, ' ', '_')ORDER BY title ASC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...