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 top 1000 AnswerId [Post Link], Score, x.CreationDate, Url, Len(replace(h.Text, Url, '')) LengthWithoutUrl
from
(select 
  a.Id as AnswerId,
  a.CreationDate,
  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 Score >= ##MinScore?5##
  -- ignore locked questions
  and isnull((select top 1 PostHistoryTypeId from PostHistory where PostId=a.ParentId and PostHistoryTypeId in (14,15,35) order by CreationDate desc),0) not in (14,35)  
  -- speed this up a bit by ignoring long answers
  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 Len(replace(h.Text, Url, '')) <= ##MaxBodyLengthWithoutUrl?29##
order by x.CreationDate desc

Enter Parameters

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