0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

WITH link_start 
     AS (SELECT [id], 
                Charindex('http://blog.stackoverflow.com', body) start_pos, 
                body 
         FROM   posts 
         WHERE  body LIKE '%http://blog.stackoverflow.com%'), 
     link_end 
     AS (SELECT id, 
                start_pos, 
                Charindex('">', body, start_pos)              closetag, 
                Charindex('" rel="nofollow', body, start_pos) nofollow, 
                body 
         FROM   link_start) 
SELECT id, 
       start_pos, 
       closetag, 
       nofollow, 
       CASE 
         WHEN nofollow <> 0 THEN Substring(body, start_pos, nofollow - start_pos 
                                 ) 
         WHEN closetag <> 0 THEN Substring(body, start_pos, closetag - start_pos 
                                 ) 
       END link 
FROM   link_end 
ORDER BY Link

fork query

edited apr 17 12

created apr 17 12 conradfrix