1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select [Url]
     , SiteName
     , DatabaseName
from
(
  select (pid - 8) pid
     , value [Url]
     , Lead(value, 1) over (order by pid) [SiteName]
     , Lead(value, 2) over (order by pid) [DatabaseName]
  from (
    SELECT row_number() over(order by @@identity ) pid
         , LEFT(TRIM(VALUE), 100) Value FROM STRING_SPLIT(
    -- fetch Markdown for post containing table with site details
    (SELECT TOP 1  Text
     FROM [StackExchange.Meta].dbo.PostHistory
     WHERE PostId = 359796 AND PostHistoryTypeId = 5
     ORDER BY CreationDate DESC)
    , '|')
    WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0
  ) src
  where pid >= 8
) data
where pid % 3 = 0
and url like 'https://%' -- https://meta.stackexchange.com/posts/359796/revisions

-- without where / check last line
select [Url]
     , SiteName
     , DatabaseName
from
(
  select (pid - 8) pid
     , value [Url]
     , Lead(value, 1) over (order by pid) [SiteName]
     , Lead(value, 2) over (order by pid) [DatabaseName]
  from (
    SELECT row_number() over(order by @@identity ) pid
         , LEFT(TRIM(VALUE), 100) Value FROM STRING_SPLIT(
    -- fetch Markdown for post containing table with site details
    (SELECT TOP 1  Text
     FROM [StackExchange.Meta].dbo.PostHistory
     WHERE PostId = 359796 AND PostHistoryTypeId = 5
     ORDER BY CreationDate DESC)
    , '|')
    WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0
  ) src
  where pid >= 8
) data
where pid % 3 = 0

fork query

edited feb 7 21

created feb 7 21 rene