0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for gardeners and landscapers

--Finds Posts with images ranked by score
DECLARE @IL VARCHAR(MAX)
SET @IL = 'http://i.imgur.com/'

declare @PostsWithImages table (Score int, Id int, Body VARCHAR(MAX))
INSERT @PostsWithImages
--SELECT TOP 5
--  Score AS "Score",
--  Id AS [Post Link],
--  Body
--FROM
--  Posts
--WHERE
--  PostTypeId = 2 AND
--  Body LIKE '%' + @IL + '%'
SELECT 5, 1, 'Hello"http://i.imgur.com/dog.jpg" a pretty dog "http://i.imgur.com/dog2.jpg" wowser'
UNION ALL
SELECT 4, 2, 'Wowee "http://i.imgur.com/image.png"'
--SUBSTRING(Body,
--            CHARINDEX(@IL,Body),
--            CHARINDEX(' ',Body,CHARINDEX(@IL,Body))-CHARINDEX(@IL,Body)-1) AS [Image Link]

;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 
) SELECT * FROM ImageLinks

fork query

created may 3 11 ihatetimemachines