1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select id [Post Link]
from (
select id
     , line
     , lag(pos) over( partition by id order by line) [prev]
     , lead(pos) over( partition by id order by line) [next]
     , pos
     , wlen
     , value
from
(
select row_number() over (partition by id order by id) line
     , *
from
(
select p.id 
     , md.text
     , charindex('>',words.value) pos
     , len(words.value) wlen
     , words.value
from posts p
inner join users u on u.id = p.owneruserid
inner join (
  select id, postid, text
  from posthistory ph
  where id = (select max(id) 
            from posthistory phm
            where posthistorytypeid in (2,5,8)
            and phm.postid = ph.postid)
) md on md.postid = p.id
cross apply string_split(md.text, char(10)) words
where body like '%<blockquote>%'
and u.accountid = 169656 
) data
) linewords
) alldata
where wlen = 1 --empty line
and next = 1 -- next line starts with a > 
and prev = 1 -- prev line starts with a >

fork query

edited sep 12 20

created sep 12 20 rene