2

Please login or register to vote for this query.

(click on this box to dismiss)

Find tags that differ only in hyphenation or pluralization

Q&A for professional and enthusiast programmers

SELECT
  t1.Count AS [Count 1],
  CASE WHEN Len(e1.Body) > 0 THEN Len(e1.Body) ELSE NULL END AS [Excerpt 1],
  CASE WHEN Len(w1.Body) > 0 THEN Len(w1.Body) ELSE NULL END AS [Wiki 1],
  'site://tags/' + t1.TagName + '|' + t1.TagName AS [Tag 1],
  t2.Count AS [Count 2],
  CASE WHEN Len(e2.Body) > 0 THEN Len(e2.Body) ELSE NULL END AS [Excerpt 2],
  CASE WHEN Len(w2.Body) > 0 THEN Len(w2.Body) ELSE NULL END AS [Wiki 2],
  'site://tags/' + t2.TagName + '|' + t2.TagName AS [Tag 2]
FROM Tags t1 LEFT JOIN Posts e1 ON t1.ExcerptPostId = e1.Id
             LEFT JOIN Posts w1 ON t1.WikiPostId = w1.Id,
     Tags t2 LEFT JOIN Posts e2 ON t2.ExcerptPostId = e2.Id
             LEFT JOIN Posts w2 ON t2.WikiPostId = w2.Id
WHERE
  -- t1 is the tag with the highest use count, or failing that the
  -- first in lexicographic order
  (t1.Count > t2.Count OR
   (t1.Count = t2.Count AND t1.TagName < t2.TagName)) AND
  -- Exclude very short tag names, as they seem to be mostly false positives
  -- see http://data.stackexchange.com/stackoverflow/query/72995/short-tag-names-that-exist-both-with-and-without-a-final-s
  Len(t1.TagName) >= 4 AND Len(t2.TagName) >= 4 AND
  -- Strip hyphens, and strip 's' before a hyphen or at the end
  Replace(Replace(t1.TagName + '-', 's-', ''), '-', '') =
  Replace(Replace(t2.TagName + '-', 's-', ''), '-', '')
ORDER BY t2.Count DESC,
         t1.Count DESC,
         t1.TagName ASC,
         t2.TagName ASC

fork query

edited jun 14 12

created jun 13 12 Gilles