USE zhwiktionary_p;

SELECT page_namespace, page_id, CONCAT(case

                                          when page_namespace = 10 then "Template:"

                                          when page_namespace = 11 then "Template talk:"

                                          when page_namespace = 828 then "Module:"

                                          when page_namespace = 829 then "Module talk:"

                                          end, page_title) AS 'real title', rd_title

FROM page

LEFT JOIN redirect ON page.page_id = redirect.rd_from

WHERE page_title REGEXP '^[a-z]'

    AND (page_namespace = 10 OR page_namespace = 11 OR page_namespace = 828 OR page_namespace = 829)

    AND page_is_redirect = 1

    AND CONCAT(UCASE(SUBSTRING(CONVERT(page.page_title USING utf8),1,1)), SUBSTRING(page.page_title,2,LENGTH(page.page_title)-1)) != redirect.rd_title