プログラマーとプログラミングに熱心な人々のための質問と回答
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 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 - 2); 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 TagName, Link FROM @tag_link_table WHERE Link NOT LIKE '%isfdb%' AND Link NOT LIKE '%alastairreynolds%' AND Link NOT LIKE '%georgerrmartin%' ORDER BY TagName