Q&A for people interested in the history and origins of science and mathematics
DECLARE @tag_link_table TABLE (TagName nvarchar(35), Link nvarchar(max)); DECLARE @tag_name nvarchar(35); DECLARE @tag_wiki_id int; DECLARE @tag_wiki nvarchar(max); DECLARE @link_index int; DECLARE @link_length int; DECLARE tag_cursor CURSOR FOR SELECT TagName, WikiPostId FROM Tags --WHERE TagName LIKE '%isaac%' OPEN tag_cursor; FETCH NEXT FROM tag_cursor INTO @tag_name, @tag_wiki_id; WHILE @@FETCH_STATUS = 0 BEGIN SET @tag_wiki = (SELECT TOP(1) Body FROM Posts WHERE Id = @tag_wiki_id); SET @link_index = PATINDEX('%http:%', @tag_wiki); WHILE @link_index > 0 BEGIN SET @tag_wiki = SUBSTRING(@tag_wiki, @link_index, LEN(@tag_wiki) - @link_index); INSERT INTO @tag_link_table VALUES(@tag_name, @tag_wiki); SET @tag_wiki = SUBSTRING(@tag_wiki, 2, LEN(@tag_wiki)); SET @link_index = PATINDEX('%http:%', @tag_wiki); END FETCH NEXT FROM tag_cursor INTO @tag_name, @tag_wiki_id; END CLOSE tag_cursor; DEALLOCATE tag_cursor; SELECT * FROM @tag_link_table ORDER BY TagName