Find images not hosted on i.stack.imgur.com

0

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

WITH CTE AS (
  SELECT Id,
    SUBSTRING(Body, CHARINDEX('<img src="', Body) + 10, CHARINDEX('"', Body, CHARINDEX('<img src="', Body) + 10) - CHARINDEX('<img src="', Body) - 10) AS Img,
    SUBSTRING(Body, CHARINDEX('"', Body, CHARINDEX('<img src="', Body) + 10), LEN(Body)) AS Rest
  FROM   Posts
  WHERE  Body LIKE '%<img src%'
         AND OwnerUserId = ##UserId##
  UNION ALL
  SELECT Id,
    SUBSTRING(Rest, CHARINDEX('<img src="', Rest) + 10, CHARINDEX('"', Rest, CHARINDEX('<img src="', Rest) + 10) - CHARINDEX('<img src="', Rest) - 10) AS Img,
    SUBSTRING(Rest, CHARINDEX('"', Rest, CHARINDEX('<img src="', Rest) + 10), LEN(Rest)) AS Rest
  FROM   CTE
  WHERE  Rest LIKE '%<img src%'
 )
SELECT Id AS [Post Link], Img AS [Image Link]
FROM CTE
WHERE Img NOT LIKE '%://i.stack.imgur.com/%';

Enter Parameters

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