Tag wikis with http links

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts Meta

Q&A about the site for enthusiasts and power users of the Android operating 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%'
AND Link NOT LIKE '%heinleinsociety.org%'
AND Link NOT LIKE '%heinleinarchives.net%'
AND Link NOT LIKE '%robert-silverberg.com%'
AND Link NOT LIKE '%meganlindholm.com%'
AND Link NOT LIKE '%robinhobb.com%'
AND Link NOT LIKE '%fantasyliterature.com%'
AND Link NOT LIKE '%terrybrooks.net%'
AND Link NOT LIKE '%sisterclaire.com%'
AND Link NOT LIKE '%skin-horse.com%'
AND Link NOT LIKE '%narbonic.com%'
AND Link NOT LIKE '%starslip.chainsawsuit.com%'
AND Link NOT LIKE '%swtor.com%'
AND Link NOT LIKE '%stephen-baxter.com%'
AND Link NOT LIKE '%lspace.org%'
GROUP BY TagName
ORDER BY TagName

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results