select substring(url,1, charindex('/', url, 9)) as site ,...

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 substring(url,1, charindex('/', url, 9)) as site
     , count(*)
from (
select top 1000000
       id 
    -- , charindex('<a href="http',body) as start
    -- , charindex('"', body, charindex('<a href="http',body) + 16) as endi
     , substring(
         body, 
         charindex('<a href="http',body) + 9, 
         charindex('"', body, charindex('<a href="http',body) + 16) -
         (charindex('<a href="http',body) + 9) ) + '/' url
     , body
from posts
where posttypeid = 2 -- A
and score > 0
and body like '%<a href="http://%'
) as answers
group by substring(url,1, charindex('/', url, 9))
order by count(*) desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results