How can I find my posts where the headers are now busted after the migration to CommonMark

Question

CommonMark is a bit more strict than the previous markdown flavor.

One of these cases is the markdown for headers to generate an H1, H2 or H3. A # on a new line followed by text was all that was needed, for example:

###Header3

There was no requirement that the # needed to be followed by a space. With the switch to CommonMark this has changed and a space is mandatory.

I was a notorious no-space user.

Is there a SEDE query that can show me the posts where I most likely used header markup that is now rendered incorrectly?

Answer 1

Yes, you can inspect the Body of the Posts table. You'll find the following html content in that field for a broken header:

<p>###Header3
Lorum ipsum
</p>

There is a new line (char(10)) after the header.

With this knowledge the following query returns your most likely broken posts:

select id as [Post Link]  
from posts
where owneruserid = ##userid?811##
and (
body like concat('%', nchar(10), '<p>###[^ ]%' , nchar(10), '%')
or body like concat('%', nchar(10), '<p>##[^ ]%' , nchar(10), '%')
or body like concat('%', nchar(10), '<p>#[^ ]%' , nchar(10), '%')
)

Due to the naïve way of parsing you might have some false positives.

I leave it to others to maybe reduce the false positive rate by inspecting the actual markdown the PostHistory table.

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.