0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A 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_index int;
DECLARE @link_length 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);
    WHILE LEN(@tag_wiki) > 0
    BEGIN
        SET @link_index = PATINDEX('%http:%', @tag_wiki);
        IF @link_index > 0
        BEGIN
            SET @link_length = LEN(@tag_wiki) - @link_index;
            SET @tag_wiki = SUBSTRING(@tag_wiki, @link_index, @link_length);
            INSERT INTO @tag_link_table VALUES(@tag_name, @tag_wiki);
            
            SET @tag_wiki = SUBSTRING(@tag_wiki, @link_length, LEN(@tag_wiki) - @link_length);
        END
        ELSE
        BEGIN
            BREAK
        END
    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
ORDER BY TagName

fork query

edited aug 23 19

created jun 12 19 anonymous