Titles w/o serial commas

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

Page titles that exist in mainspace and don't have a serial comma (but look like they could), whether they're redirects, and what the titles would be if the serial comma was added.

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

SQL

WITH p2 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_added
  FROM page
  WHERE page_namespace = 0
    AND REPLACE(page_title, '_', ' ') REGEXP '\\w, (?:\\w| )+ (?:and|or) \\w'
)
SELECT p2.*,
       CASE
         WHEN page_id IS NULL THEN 'doesn''t exist'
         WHEN page_is_redirect = 1 THEN 'redirect'
         ELSE 'non-redirect'
       END AS comma_added_status
FROM p2
LEFT JOIN page ON page_namespace = 0 AND page_title = REPLACE(comma_added, ' ', '_')
ORDER BY title ASC;

AخA
 
WITH p2 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_added  FROM page  WHERE page_namespace = 0    AND REPLACE(page_title, '_', ' ') REGEXP '\\w, (?:\\w| )+ (?:and|or) \\w')SELECT p2.*,       CASE         WHEN page_id IS NULL THEN 'doesn''t exist'         WHEN page_is_redirect = 1 THEN 'redirect'         ELSE 'non-redirect'       END AS comma_added_statusFROM p2LEFT JOIN page ON page_namespace = 0 AND page_title = REPLACE(comma_added, ' ', '_')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...