Posts with links to images, instead of the images themselves

0

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

SELECT TOP ##NumberOfPosts:int?50## p.Score, p.Id AS [Post Link],
       p.CreationDate, p.LastActivityDate,
       u.Reputation AS [Author's current reputation]
FROM   Posts p LEFT OUTER JOIN Users u on p.OwnerUserId = u.Id
WHERE  1=1

       -- Only needed on Stack Overflow
       --AND p.CreationDate >= DATEADD(DAY, -28, GETDATE())
       
       -- Edited at least 6 months ago (Archaeologist badge)
       --AND p.LastActivityDate <= DATEADD(DAY, -185, GETDATE())

       --Search for posts by users with relatively low reputation, high reputation users usually know what they're doing
       AND 50 < u.Reputation AND u.Reputation < 2000

       --If the link is already an image we don't have to do anything
       AND NOT p.Body LIKE '%<img src%'

       --If the OP has enough reputation to post lots of links, he has enough reputation to post images
       AND NOT p.Body LIKE '%<a %<a %'
       AND     p.Body LIKE '%<a href=%imgur.com/%.%>%</a>%'
ORDER BY p.Score DESC

Enter Parameters

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