0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for science fiction and fantasy enthusiasts

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/%';

fork query

edited may 14 anonymous

created apr 25 23 41686d6564