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