-- 2> /dev/null; mysql -h commonswiki.analytics.db.svc.eqiad.wmflabs  <<< '
/* HTML entities in file names
 * Run time: 1 min
 */
/* https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references */
SET @entities = "quot|amp|apos|lt|gt|nbsp|iexcl|cent|pound|curren|yen|brvbar|"
"sect|uml|copy|ordf|laquo|not|shy|reg|macr|deg|plusmn|sup2|sup3|acute|micro|"
"para|middot|cedil|sup1|ordm|raquo|frac14|frac12|frac34|iquest|Agrave|Aacute|"
"Acirc|Atilde|Auml|Aring|AElig|Ccedil|Egrave|Eacute|Ecirc|Euml|Igrave|Iacute|"
"Icirc|Iuml|ETH|Ntilde|Ograve|Oacute|Ocirc|Otilde|Ouml|times|Oslash|Ugrave|"
"Uacute|Ucirc|Uuml|Yacute|THORN|szlig|agrave|aacute|acirc|atilde|auml|aring|"
"aelig|ccedil|egrave|eacute|ecirc|euml|igrave|iacute|icirc|iuml|eth|ntilde|"
"ograve|oacute|ocirc|otilde|ouml|divide|oslash|ugrave|uacute|ucirc|uuml|yacute|"
"thorn|yuml|OElig|oelig|Scaron|scaron|Yuml|fnof|circ|tilde|Alpha|Beta|Gamma|"
"Delta|Epsilon|Zeta|Eta|Theta|Iota|Kappa|Lambda|Mu|Nu|Xi|Omicron|Pi|Rho|Sigma|"
"Tau|Upsilon|Phi|Chi|Psi|Omega|alpha|beta|gamma|delta|epsilon|zeta|eta|theta|"
"iota|kappa|lambda|mu|nu|xi|omicron|pi|rho|sigmaf|sigma|tau|upsilon|phi|chi|"
"psi|omega|thetasym|upsih|piv|ensp|emsp|thinsp|zwnj|zwj|lrm|rlm|ndash|mdash|"
"lsquo|rsquo|sbquo|ldquo|rdquo|bdquo|dagger|Dagger|bull|hellip|permil|prime|"
"Prime|lsaquo|rsaquo|oline|frasl|euro|image|weierp|real|trade|alefsym|larr|"
"uarr|rarr|darr|harr|crarr|lArr|uArr|rArr|dArr|hArr|forall|part|exist|empty|"
"nabla|isin|notin|ni|prod|sum|minus|lowast|radic|prop|infin|ang|and|or|cap|cup|"
"int|there4|sim|cong|asymp|ne|equiv|le|ge|sub|sup|nsub|sube|supe|oplus|otimes|"
"perp|sdot|lceil|rceil|lfloor|rfloor|lang|rang|loz|spades|clubs|hearts|diams";
SET @pattern = CONCAT("((&[ _-]*(x?[0-9a-fA-F]{2,5}|[0-9]{2,5}|", @entities, ");|",
    "\\^(\\^x?[0-9a-fA-F]{2,5}|", @entities, ")(,|(?=[.]{3}|_-_|[.]...$))){1,4})");

SELECT
  REPLACE(CONCAT("[[:File:", page_title, "|", REGEXP_REPLACE(
    page_title,
    @pattern,
    "<span class=error>\\1</span>"
  ), "]]"), "_", " ") AS "File",
  REGEXP_REPLACE(page_title, CONCAT(".*?", @pattern, ".*"), "\\1") AS HTML_Entity,
  CONCAT("[[User:", rev_user_text, "|", rev_user_text, "]]") AS "User",
  DATE_FORMAT(rev_timestamp, "%Y-%m-%d") AS Uploaded,
  IF(rev_comment LIKE "%{{%"
  OR rev_comment LIKE "%{{int:%"
  OR rev_comment LIKE "%Fleuron%"
  OR rev_comment LIKE "%Portable Antiquities Scheme%",
   "",
   REPLACE(rev_comment, "\n", " ")
  ) AS "Comment"
FROM page
JOIN revision ON rev_page=page_id AND rev_parent_id=0
WHERE page_namespace=6 /*File:*/
AND (page_title LIKE "%&%;%" OR page_title LIKE "%^%")
AND page_is_redirect=0
GROUP BY page_title
HAVING HTML_Entity != page_title /* Failed match */
ORDER BY rev_timestamp DESC;
-- ' --html commonswiki_p > ~/public_html/title_entity.html