0

Please login or register to vote for this query.

(click on this box to dismiss)

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 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##
order by x.CreationDate desc

fork query

edited aug 23 16

created aug 22 16 anonymous