0

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.

Q&A for enthusiasts and power users of the Android operating system

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

fork query

edited jul 22 17 jon.doe26210

created apr 3 14 senshin