Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Bdijkstra
.
1. Finds red links that start with "Ij" while a blue link exists with "IJ". 2. Finds pages that start with "IJ", without a redirect to it(s target) that start with "Ij" TODO: use pagelinks to find actual links.
Toggle Highlighting
SQL
USE nlwiki_p; /*SELECT page_namespace AS 'src-ns', page_title AS 'src-title', pl_namespace AS 'tgt-ns', pl_title AS 'tgt-title' FROM `pagelinks`, `page` ps WHERE pl_namespace NOT IN (2,3) AND # no links to user namespaces pl_title REGEXP '^Ij' AND # target title starts with 'Ij' NOT EXISTS ( # target page doesn't exist SELECT * FROM `page` pt WHERE pt.page_namespace=pl_namespace AND pt.page_title=pl_title ) AND EXISTS ( # correctly capitalized target does exist SELECT * FROM `page` pt WHERE pt.page_namespace=pl_namespace AND pt.page_title=BINARY CONCAT(_UTF8MB4 'IJ', SUBSTRING(pl_title, 3)) ) AND pl_from=ps.page_id # acquire source page title ORDER BY pl_namespace, pl_title*/ SELECT pt.page_namespace AS 'ns', pt.page_title AS 'title', pt.page_is_redirect AS 'redir' FROM redirect, page pt WHERE rd_from=pt.page_id AND pt.page_title REGEXP '^IJ' AND (( pt.page_is_redirect=0 AND NOT EXISTS ( SELECT * FROM redirect, page ps WHERE rd_from=ps.page_id AND rd_namespace=pt.page_namespace AND rd_title=pt.page_title AND ps.page_title=BINARY CONCAT(_UTF8MB4 'IJ', SUBSTRING(rd_title, 3)) ) ) OR ( pt.page_is_redirect=1 AND NOT EXISTS ( SELECT * FROM redirect rs, redirect rt2, page ps, page pt2 WHERE pt2.page_id=rt2.rd_from AND rs.rd_from=ps.page_id AND rs.rd_namespace=pt2.page_namespace AND rs.rd_title=pt2.page_title AND ps.page_title=BINARY CONCAT(_UTF8MB4 'IJ', SUBSTRING(rs.rd_title, 3)) ) )) ORDER BY ns, title
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
.
Checking query status...