0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

DECLARE @posts TABLE (Id int, PostType tinyint, ParentId int, Link nvarchar(max));

DECLARE @post_id int;
DECLARE @post_type_id tinyint;
DECLARE @post_parent_id int;
DECLARE @post_body nvarchar(max);

DECLARE post_cursor CURSOR FOR
SELECT Id, PostTypeId, ParentId, Body
FROM Posts
WHERE PostTypeId IN (1, 2)
  AND Body LIKE '%href="http:%'
  AND OwnerUserId = ##UserId:int##

OPEN post_cursor;

DECLARE @link nvarchar(max);
DECLARE @link_index int;
DECLARE @link_end int;

FETCH NEXT FROM post_cursor
INTO @post_id, @post_type_id, @post_parent_id, @post_body;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @link_index = PATINDEX('%href="http:%', @post_body);
    WHILE @link_index > 0
    BEGIN
        SET @post_body = SUBSTRING(@post_body, @link_index, LEN(@post_body) - @link_index);
        
        SET @link_end = PATINDEX('% %', @post_body);
        IF @link_end > 0
             SET @link = SUBSTRING(@post_body, 1, @link_end - 2);
        ELSE
             SET @link = @post_body;
        
        INSERT INTO @posts VALUES(@post_id, @post_type_id, @post_parent_id, @link);
        
        SET @post_body = SUBSTRING(@post_body, 2, LEN(@post_body));
        SET @link_index = PATINDEX('%href="http:%', @post_body);
    END
   
    FETCH NEXT FROM post_cursor
    INTO @post_id, @post_type_id, @post_parent_id, @post_body;
END

CLOSE post_cursor;
DEALLOCATE post_cursor;

SELECT
  Id AS [Post Link],
  PostType,
  COUNT(*) AS Links
FROM @posts
WHERE Link NOT LIKE '%isfdb%'
GROUP BY
  Id,
  PostType
ORDER BY Links DESC

fork query

edited aug 11 SQB

created jun 12 19 anonymous