Find some link-mostly answers that are positively received

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select u.AnswerId [Post Link] from (
select top 1000 AnswerId, ParentId, Score, x.CreationDate, Url, Len(replace(h.Text, Url, '')) LengthWithoutUrl
from
(select 
  a.Id as AnswerId,
  a.CreationDate,
  a.ParentId,
  a.Score,
  -- I would expand and comment the expression below, but I don't want to encourage anyone to do string manipulation in SQL. 
  substring(a.Body, charindex('<a href="', a.Body)+9, (charindex('"', a.Body, charindex('<a href="', a.Body)+9)-charindex('<a href="',a.Body)-9)) Url,
  (select max(Id) from PostHistory where PostId=a.Id and PostHistoryTypeId in (2,5)) LatestPostHistoryId
from
  Posts a
where
  a.PostTypeId = 2
  and Len(a.Body) < ##MaxAnswerLength?512##
  and a.Body like '%<a href="%'
  and a.Body not like '%</code>%'
  
  ) x 
join PostHistory h on Id=LatestPostHistoryId
where Score >= ##MinScore?5##
and Len(replace(h.Text, Url, '')) <= ##MaxBodyLengthWithoutUrl?29##
) u
join posts q ON q.id = u.ParentId
and q.ClosedDate is not null;

Enter Parameters

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