Embedded Image Stats

1

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- Results tab 1 is list of non stack imgur images
-- Results tab 2 is breakdown by image host

CREATE TABLE #ImageLinksTemp (Id int, CreationDate datetime, Link nvarchar(max), Idx int);

WITH ImageLinks AS (
  SELECT 
    Id,
    CreationDate,
    SUBSTRING(Body, PATINDEX('%<img src="%', Body) + 10, LEN(Body)) link,
    1 idx
  FROM
    Posts
  WHERE
    Body LIKE '%<img src="%'    
  UNION ALL 
  SELECT
    Id,
    CreationDate,
    SUBSTRING(link, PATINDEX('%<img src="%', link) + 10, LEN(link)) link,
    idx + 1 idx
  FROM
    ImageLinks
  WHERE
    link LIKE '%<img src="%'
    AND idx < 100 -- limit recursion depth for obnoxious posts like https://meta.stackexchange.com/a/135347
)

-- Purpose of temp table is only so above cte results can be used in both
-- queries below, otherwise we could just use ImageLinks directly.
INSERT INTO #ImageLinksTemp SELECT * FROM ImageLinks

-- List of images:
SELECT 
  Id,
  Id [Post Link],
  CreationDate,
  LEFT(link, CHARINDEX('"', link) - 1) ImageLink
FROM 
  #ImageLinksTemp
WHERE
  link NOT LIKE 'https://i.stack.imgur.com/%'

-- By host:
SELECT
  LEFT(link, CHARINDEX('/', link, LEN('https://') + 1) - 1) Host,
  COUNT(*) Images
FROM
  #ImageLinksTemp
--WHERE
--  link NOT LIKE 'https://i.stack.imgur.com/%'
GROUP BY
  LEFT(link, CHARINDEX('/', link, LEN('https://') + 1) - 1)
ORDER BY
  Images DESC


/*
-- displays # of images per post, used when debugging
SELECT
  Id,
  (LEN(Body) - LEN(REPLACE(Body, '<img src="', ''))) / LEN('<img src="') Images
FROM
  Posts
ORDER BY
  Images DESC*/

Enter Parameters

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