1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH
TolkienTags AS (
  SELECT
    pt.PostId,
    pt.TagId,
    t.TagName
  FROM Tags t
  JOIN PostTags pt ON (t.Id = pt.TagId)
  WHERE t.TagName IN ('j-r-r-tolkien', 'tolkiens-legendarium', 'the-silmarillion', 'the-lord-of-the-rings', 'the-hobbit')
),
TolkienPosts AS (
  SELECT
    p.Id AS PostId,
    LOWER(CONCAT(p.Title, p.Body)) AS Body
  FROM Posts p
  JOIN TolkienTags tt ON (p.Id = tt.PostId)
  WHERE p.PostTypeId = 1
  UNION
  SELECT
    p.Id,
    LOWER(p.Body)
  FROM Posts p
  JOIN TolkienTags tt ON (p.ParentId = tt.PostId)
  WHERE p.PostTypeId = 2
  UNION
  SELECT
    p.Id,
    LOWER(p.Body)
  FROM Posts p
  WHERE p.Body LIKE '%Tolkien%'
    AND p.PostTypeId NOT IN (1, 2)
),
Misspellings AS (
  SELECT
    tp.PostId,
    'Silmarillion / Silmaril' AS IntendedWord,
    'Missing first L' AS Misspelling
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%simari%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion / Silmaril',
    'Single second L'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marili%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion',
    'Single second L and missing final I'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marilo%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion',
    'Single second L and missing final I and final O is A'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marilan%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion',
    'Missing final I'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marillo%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion',
    'Final O is A'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marillian%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion',
    'Final O is A and missing final I'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marillan%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion / Silmaril',
    'Double M instead of LM'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%simmar%'
  UNION
  SELECT
    tp.PostId,
    'Silmarillion / Silmaril',
    'Double R'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%marril%'
  UNION
  SELECT
    tp.PostId,
    'Tolkien',
    'Switched ie'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%olkein%'
  UNION
  SELECT
    tp.PostId,
    'Barad-dûr',
    'As one word'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%barad[uû]r%'
  UNION
  SELECT
    tp.PostId,
    'Barad-dûr',
    'As one word'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%baradd[uû]r%'
  UNION
  SELECT
    tp.PostId,
    'Barad-dûr',
    'Missing d'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%barad [uû]r%'
  UNION
  SELECT
    tp.PostId,
    'Barad-dûr',
    'Missing circumflex'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%barad-dur%'
  UNION
  SELECT
    tp.PostId,
    'Dol Guldur',
    'Added circumflex'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%dol guldûr%'
  UNION
  SELECT
    tp.PostId,
    'Dol Guldur',
    'O instead of u'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%dol guldor%'
  UNION
  SELECT
    tp.PostId,
    'Dol Guldur',
    'Added hyphen'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%dol-guld%'
  UNION
  SELECT
    tp.PostId,
    'Eärendil',
    'Missing ä'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%erendil%'
  UNION
  SELECT
    tp.PostId,
    'Eärendil',
    'No diaeresis on a'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%earendil%'
  UNION
  SELECT
    tp.PostId,
    'Aulë',
    'No diaeresis on e'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%aule%'
  UNION
  SELECT
    tp.PostId,
    'Ilúvatar',
    'No accent aigu on u'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%uvatar%'
  UNION
  SELECT
    tp.PostId,
    'Ilúvatar',
    'Double l'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%ill_vatar%'
  UNION
  SELECT
    tp.PostId,
    'Lothlórien',
    'Missing l'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%loth[oó]rien%'
  UNION
  SELECT
    tp.PostId,
    'Lothlórien',
    'No accent aigu on o'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%lothlorien%'
  UNION
  SELECT
    tp.PostId,
    'Lothlórien',
    'No accent aigu on o'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%lothorien%'
  UNION
  SELECT
    tp.PostId,
    'Lórien',
    'No accent aigu on o'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%lorien%'
  UNION
  SELECT
    tp.PostId,
    'Saruman',
    'Extra u'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%sauruman%'
  UNION
  SELECT
    tp.PostId,
    'Sauron',
    'Switched au'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%suaron%'
  UNION
  SELECT
    tp.PostId,
    'Maedhros',
    'No h'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%maedros%'
  UNION
  SELECT
    tp.PostId,
    'Númenórean',
    'I for e'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%n[úu]menorian%'
  UNION
  SELECT
    tp.PostId,
    'Númenor / Númenórean',
    'Extra r'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%n[úu]mern[oó]%'
  UNION
  SELECT
    tp.PostId,
    'Númenor / Númenórean',
    'No accent aigu on u'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%numen[oó]r%'
  UNION
  SELECT
    tp.PostId,
    'Númenórean',
    'No accent aigu on o'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%n[úu]menorean%'
  UNION
  SELECT
    tp.PostId,
    'Nazgûl',
    'No circumflex on u'
  FROM TolkienPosts tp
  WHERE tp.Body LIKE '%nazgul%'
),
MisspelledWords AS (
  SELECT
    mp.PostId,
    mp.IntendedWord,
    STRING_AGG(CONVERT(NVARCHAR(max), mp.Misspelling), ', ')
      WITHIN GROUP (ORDER BY mp.Misspelling ASC) AS Misspellings
  FROM Misspellings mp
  GROUP BY
    mp.PostId,
    mp.IntendedWord
),
MisspelledPosts AS (
  SELECT
    mw.PostId,
    STRING_AGG(
      CONVERT(
        NVARCHAR(max),
        CONCAT(mw.IntendedWord, ': ', mw.Misspellings)
      ),
      '; '
    ) AS Misspellings
  FROM MisspelledWords mw
  GROUP BY mw.PostId
)
SELECT
  pt.name AS [Post Type],
  p.Id AS [Post Link],
  STRING_AGG(CONVERT(NVARCHAR(max), tt.TagName), ', ')
      WITHIN GROUP (ORDER BY CASE tt.TagName WHEN 'tolkiens-legendarium' THEN 'aaaa' ELSE tt.TagName END ASC)
      AS Tags,
  m.Misspellings,
  p.Body
FROM MisspelledPosts m
JOIN Posts p ON (m.PostId = p.Id)
JOIN PostTypes pt ON (p.posttypeid = pt.id)
LEFT JOIN TolkienTags tt ON (p.Id = tt.PostId OR p.ParentId = tt.PostId)
GROUP BY
  pt.Name,
  p.Id,
  p.ParentId,
  p.PostTypeId,
  m.Misspellings,
  p.Body
ORDER BY
  CASE p.PostTypeId WHEN 2 THEN p.ParentId ELSE p.Id END,
  p.PostTypeId

fork query

edited mar 9 22

created feb 9 22 SQB