1

Please login or register to vote for this query.

(click on this box to dismiss)

For the list of non-series tags, see: https://data.stackexchange.com/anime/query/1863699.

Q&A for scientists using computers to solve scientific problems

WITH tagnames AS (
    SELECT 
          Tags.TagName
        , TagSynonyms.SourceTagName 
    FROM Tags
    LEFT JOIN TagSynonyms
        ON Tags.TagName LIKE '%' + TagSynonyms.TargetTagName + '%'
WHERE Tags.TagName IN (
 'aimer',
 'barasui',
 'hayao-miyazaki',
 'hideaki-anno',
 'hiroyuki-sawano',
 'jun-maeda',
 'kajiura-yuki',
 'kazuki-nakashima',
 'kazuki-takahashi',
 'ken-akamatsu',
 'kouji-seo',
 'maaya-sakamoto',
 'makoto-shinkai',
 'mamiko-noto',
 'mamoru-hosoda',
 'mamoru-oshii',
 'masamune-shirow',
 'sachika-misawa',
 'shun-saeki',
 'domo-kun',
 'hatsune-miku',
 'os-tan',
 'super-sonico',
 'crunchyroll',
 'funimation',
 'gainax',
 'key',
 'kyoto-animation',
 'madhouse',
 'netflix',
 'sentai-filmworks',
 'studio-ghibli',
 'type-moon',
 'australia',
 'england',
 'europe',
 'germany',
 'greece',
 'india',
 'japan',
 'north-korea',
 'singapore',
 'spain',
 'united-states',
 'anime-expo',
 'anime-no-chikara',
 'anime-planet',
 'claris',
 'comiket',
 'myanimelist',
 'pixiv',
 'ribon',
 'shonen-jump',
 'vocaloid'
)
)
SELECT
      Posts.Id AS [Post Link]
    , Tags
    , TagName
    , SourceTagName
FROM Posts
INNER JOIN tagnames
    ON Posts.PostTypeId = 1
    AND Posts.Tags NOT LIKE '%' + tagnames.TagName + '%'
WHERE
    LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
        + REPLACE(tagnames.TagName, '-', ' ') 
        + '[^a-zA-Z]%' 
        OR LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
            + REPLACE(tagnames.SourceTagName, '-', ' ') 
            + '[^a-zA-Z]%' 
ORDER BY 
      TagName
    , Posts.Id

view current version

edited oct 1

created oct 1 Gao