Evil Images

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

-- Evil Images

DECLARE @IL VARCHAR(MAX)
SET @IL = 'http://i.imgur.com/'

declare @PostsWithImages table (Score int, Id int, Body VARCHAR(MAX))
INSERT @PostsWithImages
SELECT TOP 50
  Score AS "Score",
  Id AS [Post Link],
  Body
FROM
  Posts
WHERE
  PostTypeId = 2 AND
  Body LIKE '%' + @IL + '%'

;WITH ImageLinks (Score, Id, [Image Link], Body) AS (
  SELECT Score,
    Id,
    SUBSTRING(Body,
              CHARINDEX(@IL,Body),
              CHARINDEX('"',Body,CHARINDEX(@IL,Body))-CHARINDEX(@IL,Body)),
    SUBSTRING(Body,CHARINDEX('"',Body,CHARINDEX(@IL,Body)), LEN(BODY)-CHARINDEX('"',Body,CHARINDEX(@IL,Body)))
  FROM @PostsWithImages 
  UNION ALL
  SELECT Score,
    Id,
    SUBSTRING(Body,
              CHARINDEX(@IL,Body),
              CHARINDEX('"',Body,CHARINDEX(@IL,Body))-CHARINDEX(@IL,Body)),
    SUBSTRING(Body,CHARINDEX('"',Body,CHARINDEX(@IL,Body)), LEN(BODY)-CHARINDEX('"',Body,CHARINDEX(@IL,Body)))
  FROM ImageLinks
  WHERE Body LIKE '%' + @IL + '%'
) SELECT Id,
         '<img src="'+LOWER([Image Link])+'"/>' AS [imgEvil]
    FROM ImageLinks
    ORDER BY Score DESC, Id

Enter Parameters

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