3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

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

SELECT
  Id AS [Post Link],
  Score,
  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,
  CreationDate
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

fork query

edited jun 10 15 Janos

created jun 8 15 rolfl