2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

select top 1000
  Post.Id as [Post Link],
  PostTypes.Name as [Type],
  case
    when Author.Id is null then Post.OwnerDisplayName
    else 'site://users/' + cast(Author.Id as varchar) + '|' + Author.DisplayName
  end as [Author],
  Post.Score,
  Post.CreationDate,
  coalesce(Post.ViewCount, Parent.ViewCount) as [Views],
  coalesce(Post.Tags, Parent.Tags) as [Tags]
from Posts as Post
  join PostTypes on Post.PostTypeId = PostTypes.Id
  left join Posts as Parent on Post.ParentId = Parent.Id
  left join Users as Author on Post.OwnerUserId = Author.Id
where Post.Body collate SQL_Latin1_General_CP1_CI_AS like '%<img src="http://%'
  and Replace(Replace(Replace(Post.Body collate SQL_Latin1_General_CP1_CI_AS, '<img src="http://i.stack.imgur.com/', 'X'), '<img src="http://i.imgur.com/', 'X'), '<img src="http://imgur.com/', 'X') like '%<img src="http://%'
  and Post.CreationDate between ##StartDate:string?2015-12-01## and ##EndDate:string?2016-01-01##

fork query

edited mar 29 18

created jan 31 16 Ilmari Karonen