Clean up your http links

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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);

-- Domains: A comma separated list of domains that are known to not have https enabled and thus cannot be fixed "Note that there should be now whitespace around the comma"
DECLARE @domains_without_https nvarchar(max);
SET @domains_without_https = ##Domains:string?isfdb.org,accio-quote.org,sf-encyclopedia.com,grinti.com,anoraksalmanac.com,supernaturalwiki.com,dolfverroen.nl,asimovonline.com,clarkesworldmagazine.com,chakoteya.net,dvdmg.com,internationalhero.co.uk,philsp.com,alisoneldred.com,darklyrics.com,michaelbarrier.com,screenertv.com,theswca.com,larryniven.net##;

-- UserId: Your user ID on the Stack you're querying
DECLARE post_cursor CURSOR FOR
SELECT Id, PostTypeId, ParentId, Body
FROM Posts
WHERE PostTypeId IN (1, 2)
  AND Body LIKE '%http:%'
  AND OwnerUserId = ##UserId:int##

OPEN post_cursor;

DECLARE @link nvarchar(max);
DECLARE @link_index int;
DECLARE @link_end int;
DECLARE @domain_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('%="http:%', @post_body);
    WHILE @link_index > 0
    BEGIN
        SET @post_body = SUBSTRING(@post_body, @link_index + 2, LEN(@post_body));
        
        SET @link_end = PATINDEX('%"%', @post_body);
        IF @link_end > 0
            SET @link = SUBSTRING(@post_body, 1, @link_end - 1);
        ELSE
            SET @link = @post_body;
        
        SET @domain_end = CHARINDEX('/', @link, 8);
        IF @domain_end > 0
            SET @link = SUBSTRING(@link, 1, @domain_end - 1);
             
        INSERT INTO @posts VALUES(@post_id, @post_type_id, @post_parent_id, @link);
        
        SET @post_body = SUBSTRING(@post_body, LEN(@link), LEN(@post_body));
        SET @link_index = PATINDEX('%="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;

WITH
DistinctLinks AS (
  SELECT
    Id,
    PostType,
    Link
  FROM @posts
  GROUP BY
    Id,
    PostType,
    Link
),
DomainsWithoutHttps AS (
  SELECT value AS Domain
  FROM STRING_SPLIT(@domains_without_https, ',')
)
SELECT
  Id AS [Post Link],
  PostType,
  COUNT(DISTINCT Link) AS DomainCount,
  STRING_AGG(Link, ', ') AS Domains
FROM DistinctLinks
LEFT JOIN DomainsWithoutHttps ON Link LIKE CONCAT('%', Domain, '%')
WHERE Domain IS NULL
GROUP BY
  Id,
  PostType
ORDER BY DomainCount DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results