What's the opposite of CodeLess Questions? Perhaps CodeOnly Answers? (Also known as code dumps.)

Inspired by the opposite question, here's the opposite query:

SELECT Id AS [Post Link], Score, CreationDate
FROM Posts
WHERE PostTypeId = 2  -- answers
  AND Body LIKE '<pre><code>%'
  AND CHARINDEX('</code></pre>', Body) + LEN('</code></pre>') = LEN(Body)
  AND ClosedDate IS NULL
ORDER BY CreationDate

Checking a couple of the results, this successfully finds the code only answers, and pretty fast.

I'm wondering if there's a better, cleaner approach that the hack with the CHARINDEX.

  • 4
    \$\begingroup\$ Feature request: Include answers like "Here's what I would do: <codedump>" \$\endgroup\$ – Simon Forsberg Jun 9 '15 at 19:19

If your aim is to find code dumps, then you can do better.

Disclaimer: the example posts linked in the following paragraphs are code dump suspects. Maybe they are code dumps, maybe not. Judge for yourselves.

In particular, answers that start with "I prefer:", or "Try:", or "Yes:", and then followed by a code dump, won't be matched by the posted query, but effectively they are still code dump suspects.

Similarly, answers that start with a code dump suspect and end with "etc...", "and so on", or "?" are also code dump suspects.

Generally speaking, a code dump suspect looks like this:

  • Contains mostly code
  • Too short (or no) introductory text
  • Too short (or no) closing text

What is too short? There cannot be rule for that. The longer the text, the more false positives, and the shorter the text, the more code dumps missed. It's probably a good idea to make the text length before and after parameters of the query.

In addition to adding these parameters to widen the search, it's natural to include the text before and after in the query result, to make it easier to judge code dump suspects at a glance.

Something like this:

DECLARE @MaxTextLenBeforeCode AS INT = ##MaxTextLenBeforeCode:int?0##;
DECLARE @MaxTextLenAfterCode AS INT = ##MaxTextLenAfterCode:int?0##;

  Id AS [Post Link],
  CHARINDEX('<pre><code>', Body) - 1 AS TextLenBeforeCode,
  LEFT(Body, CHARINDEX('<pre><code>', Body) - 1) AS TextBeforeCode,
  LEN(Body) - CHARINDEX('</code></pre>', Body) - LEN('</code></pre>') AS TextLenAfterCode,
  RIGHT(Body, LEN(Body) - CHARINDEX('</code></pre>', Body) - LEN('</code></pre>')) AS TextAfterCode,
FROM Posts
WHERE PostTypeId = 2  -- answers
  AND CHARINDEX('<pre><code>', Body) > 0
  AND CHARINDEX('<pre><code>', Body) - 1 <= @MaxTextLenBeforeCode
  AND CHARINDEX('</code></pre>', Body) + LEN('</code></pre>') >= LEN(Body) - @MaxTextLenAfterCode
  AND ClosedDate IS NULL
ORDER BY Score DESC, CreationDate
|improve this answer|||||
  • \$\begingroup\$ Now we're talking! Do you feel any performance difference between these queries? \$\endgroup\$ – Simon Forsberg Jun 10 '15 at 12:13
  • \$\begingroup\$ Nope, feels just as fast (but obviously it should be slower) \$\endgroup\$ – janos Jun 11 '15 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.