Q&A for designers and users of artificial or invented languages, consciously developed instead of having developed naturally
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 nvarchar(max); DECLARE @link_index int; DECLARE @link_end 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); SET @link_end = PATINDEX('% %', @tag_wiki); IF @link_end > 0 SET @link = SUBSTRING(@tag_wiki, 1, @link_end - 1); ELSE SET @link = @tag_wiki; INSERT INTO @tag_link_table VALUES(@tag_name, @link); 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