Q&A about the site for people interested in improving and participating in the patent system
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 FROM @tag_link_table WHERE Link NOT LIKE '%isfdb%' AND Link NOT LIKE '%alastairreynolds%' AND Link NOT LIKE '%georgerrmartin%' AND Link NOT LIKE '%fantasticmaps%' AND Link NOT LIKE '%nick.com%' AND Link NOT LIKE '%officialbeinghuman%' AND Link NOT LIKE '%tv.com%' AND Link NOT LIKE '%berserkerfan.org%' AND Link NOT LIKE '%andygriffiths.com.au%' AND Link NOT LIKE '%carlsagan.com%' AND Link NOT LIKE '%antipope.org%' AND Link NOT LIKE '%cherryh.com%' AND Link NOT LIKE '%jim-butcher.com%' AND Link NOT LIKE '%complete-review.com%' AND Link NOT LIKE '%shannonsullivan.com%' AND Link NOT LIKE '%drwhoguide.com%' AND Link NOT LIKE '%scv.bu.edu%' AND Link NOT LIKE '%tavia.co.uk%' AND Link NOT LIKE '%gutenberg.org%' AND Link NOT LIKE '%freefall.purrsia.com%' AND Link NOT LIKE '%girlgeniusonline.com%' AND Link NOT LIKE '%grishaverse.com%' AND Link NOT LIKE '%yudkowsky.net%' AND Link NOT LIKE '%hpmor.com%' AND Link NOT LIKE '%shurtugal.com%' AND Link NOT LIKE '%asimovonline.com%' AND Link NOT LIKE '%greatsfandf.com%' AND Link NOT LIKE '%theevitable.blogspot.com%' AND Link NOT LIKE '%dendarii.com%' AND Link NOT LIKE '%jacquelinecarey.com%' AND Link NOT LIKE '%loveisintheblood.com%' AND Link NOT LIKE '%tolkiengateway.net%' AND Link NOT LIKE '%mylittlewiki.org%' AND Link NOT LIKE '%namesakecomic.com%' AND Link NOT LIKE '%neilgaiman.com%' AND Link NOT LIKE '%giantitp.com%' AND Link NOT LIKE '%gregegan.net%' AND Link NOT LIKE '%patrickrothfuss.com%' AND Link NOT LIKE '%neverpedia.com%' GROUP BY TagName ORDER BY TagName