Open main menu
-- 2> /dev/null; date; sql --cluster analytics commonswiki_p <<< '
/* Double file extensions
 * http://quarry.wmflabs.org/query/894
 * CONVERT(... USING utf8mb4) for case insensitivity
 * License: Public Domain
 * Run time: 5 minutes <SLOW_OK>
 */
SELECT Filename AS "" FROM (
SELECT CONCAT("* {{/link|File:", REPLACE(REPLACE(page_title, "_", " "), "=", "&#61;"), "}}") AS Filename, page_title
FROM page
WHERE page_namespace=6 AND page_is_redirect=0
AND CONVERT(page_title USING utf8mb4) REGEXP "[.](djvu|gif|jpeg|jpg|flac|mid|mp3|wav|ogg|oga|ogv|opus|pdf|png|stl|svg|tif[^f]|tiff|xcf|webm|webp)."
AND CONVERT(page_title USING utf8mb4) NOT REGEXP "/|[.]webm[sh]d[.]webm$|[.]oggtheora[.]og[agv]$|[.]oggvorbis[.]ogg$|[.]jpg_-_panoramio[.]jpg|[.]jpg!PinterestLarge[.]jpg$|[.]jpg_[(]FindID|[.]jps[.].*[.]gif$"
UNION SELECT "----", "----"
) AS dblextension
ORDER BY
  /* .TIFFany at bottom */
  IF(page_title="----", 0.5, CONVERT(page_title USING utf8mb4) REGEXP "[.](djvu|gif|jpe?g|flac|mid|mp3|wav|og[agv]|opus|pdf|png|stl|svg|tiff?|xcf|webm|webp)[^[:alpha:]]") DESC,
  /* .svg.png or .jpeg.jpg at top */
  page_title REGEXP "[.][[:alpha:]]{3,4}[.][[:alpha:]]{3,4}$" DESC,
  /* Separate R0012131.JPG (8332852801).jpg */
  LENGTH(page_title)>24 AND LOCATE(".", REVERSE(page_title), 6) > 16,
  /* ASCIIbetical sort */
  BINARY page_title
;-- ' > ~/public_html/dblextension.txt; date;

The following files has either a double extension (e.g. .jpg.svg or .JPG".jpg) or an extension in the middle of the name (e.g. File:US Navy 020305-N-9769P-031.JPG Bombs ready on flight deck.jpg). This list needs review for mistakes (e.g. File:Singer.Model27.Tiffany.decal.jpg). Other wikis (like English Wikipedia) can use the grep tool to get similar results.