Code fences with lost text on the first line (ugly way)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

--text after a code fence opening will be lost

WITH LatestBodyRevision (PostId, PostHistoryId) AS (
     SELECT p.Id, ph.Id AS PostHistoryId
     FROM Posts p
     INNER JOIN PostHistory ph ON ph.PostId = p.Id
     WHERE ph.PostHistoryTypeId IN (2,5,8) 
       AND NOT EXISTS (
           SELECT * FROM PostHistory phtwo
           WHERE phtwo.PostHistoryTypeId IN (2,5,8) AND 
                 phtwo.PostId = p.Id AND 
                 phtwo.CreationDate > ph.CreationDate
       )
)

SELECT lbr.PostId AS [Post Link], 
       Score AS [Score], 
       ViewCount AS [Views], 
       LastActivityDate AS [Last Active],
       ph.Text AS [Text]
FROM LatestBodyRevision lbr 
INNER JOIN PostHistory ph ON lbr.PostHistoryId = ph.Id
INNER JOIN Posts p ON lbr.PostId = p.Id
WHERE ph.Text LIKE '%' + CHAR(10) + '```[^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
OR ph.Text LIKE '%' + CHAR(10) + '```[^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^' + CHAR(13) + CHAR(10) + '][^a-z' + CHAR(13) + CHAR(10) + ']%'
ORDER BY p.CreationDate ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results