CLEANUP: Find images not hosted on imgur

3

Please login or register to vote for this query.

(click on this box to dismiss)

This query will find most posts that contain at least one image that isn't hosted either on i.imgur.com or i.stack.imgur.com. Note that some images have legitimate reasons for being hosted off-site - maybe they're SVGs (not supported by imgur), or are larger than the imgur upload size limits (4 MB non-gif; 2 MB gif). Also, the [Snippet] column may list an imgur URL; this is because it just lists the URL of the first image it finds. There may still be non-imgur images elsewhere in the post.

Physics

Q&A for active researchers, academics and students of physics

SELECT
  b.Score,
  b.Id AS [Post Link],
  b.CreationDate,
  SUBSTRING(b.Body, 
            CHARINDEX('<img', b.Body) + 10, -- LEN('<img src="')
            CASE WHEN CHARINDEX('" alt', b.Body, CHARINDEX('<img', b.Body)) - CHARINDEX('<img', b.Body) - 10 >= 0
                 THEN CHARINDEX('" alt', b.Body, CHARINDEX('<img', b.Body)) - CHARINDEX('<img', b.Body) - 10
                 ELSE 0
                 END
            ) AS [Snippet],
  (LEN(b.Body) - LEN(REPLACE(b.Body, '<img', '')))/4 AS [NumImages],
  CASE WHEN b.CommunityOwnedDate IS NOT NULL THEN 'Yes' ELSE '' END AS [IsCW?]
FROM
  Posts b
WHERE
  b.Body LIKE '%<img src="http://[^i]%'
  OR b.Body LIKE '%<img src="http://i[^.]%'
  OR b.Body LIKE '%<img src="http://i.[^is]%'
ORDER BY
  b.CreationDate DESC

Enter Parameters

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