How can I find posts with particularly large code blocks in a non-code-based site?

Question

From time to time in Space SE a question or answer about a calculation will need to share a significant amount of tabular data, say 100 or 1000 lines. It needs to be forever available to future readers, and the simplest (and perhaps only) way to permanently attach the data to the post is to use the code block feature which limits its display size so it doesn't dominate the post.

While there's an effort to move some smaller tables to the new markdown tables (New Feature: Table Support) it would be helpful to find those posts that contain large code blocks and make a note of them so that they don't inadvertently get converted to markdown tables that are several meters tall.

I went to the Stack Exchange Data Explorer and did a quick search for "large code blocks" to no avail.

Question: How can we find posts with particularly large code blocks in a non-code-based site?

I suppose if we could enter a number in little a box for the minimum length that would be handy; if it has to be fixed, perhaps 50 lines of continuous code block would be enough for us. For actual code most of the time it's usually just short snippets.

Answer 1

Here is a simple query which shows the length of the first code block (in characters). It works fine on a site like Space Stack Exchange, I'm not sure about larger sites.

I have no doubt @rene can come up with something that analyses all code blocks in a post. It's a pity that STRING_SPLIT only supports single characters as separator...

Answer 2

I was challenged to provide a query that would analyze all code blocks, so here it is.

I started with the work already done by Glorfindel and then added a Recursive CTE to find the next block. That turns out to be a tad more challenging because PATINDEX lacks a start parameter. So it is down the substring rabbit hole from there. As with any recursion: you have to know when you have to stop. So I did.

declare @opentag nvarchar(20) = '<pre><code>'
declare @opentaglike nvarchar(20) = concat('%', @opentag, '%')
declare @closetag nvarchar(20) = '</code></pre>'
declare @closetaglike nvarchar(20) = concat('%', @closetag, '%')


; with CodeBlocksPerPost(id, opentagpos, closetagpos) as 
(
  select id
       , patindex(@opentaglike, Body) + len(@opentag) [opentagpos]
       , patindex(@closetaglike, Body) [closetagpos]
  from posts
  where body like @opentaglike
  union all
  select cbpp.id
       , cbpp.closetagpos 
         + patindex(
               @opentaglike
             , substring(body, closetagpos + len(@closetag), len(body))
           ) + len(@opentag) [opentagpos]
       , cbpp.closetagpos 
         + patindex(
               @closetaglike
             , substring(body, closetagpos + len(@closetag), len(body))
            ) [closetagpos]
  from CodeBlocksPerPost cbpp 
  inner join posts on posts.id = cbpp.id
  where patindex(@closetaglike
          , substring(body
               , closetagpos + len(@closetag)
               , len(body))
         ) > 0
),
CodeLinesPerBlock as (
  select codeblocksperpost.id
       , opentagpos
       , closetagpos
       , ( closetagpos 
         - opentagpos 
         - len(replace(
              substring(
                  body
                , opentagpos
                , closetagpos - opentagpos)
            , nchar(10)
            , ''))
         ) [lines]
  from CodeBlocksPerPost
  inner join posts on posts.id = CodeBlocksPerPost.id
)


select top 1000 
       id [Post Link]
     , count(*) [# of blocks]
     , max(lines) [Max # of lines] 
     , max(closetagpos - opentagpos) [Max length]
     , min(closetagpos - opentagpos) [Min length]
     , sum(closetagpos - opentagpos) [Total length]
from CodeLinesPerBlock
group by id
order by max(closetagpos - opentagpos) desc

As we now have potentially more than one row per post we can max, min and sum the length of each code block. That renders this result today:

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the unforgettable Monica Cellio.
Say "Hi" in SEDE chat.