Generic post content LIKE search

2

Please login or register to vote for this query.

(click on this box to dismiss)

Do a generic T-SQL LIKE query on post HTML content and format the results nicely. Wildcards that work include % for any string, _ for any character, [ABCDE] or [A-E] for a letter from A to E, and [^ABCDE] or [^A-E] for any character *except* A to E. To match a literal "%", "_", "[" or "]", insert a backslash ("\") before it. Remember to wrap the search pattern in % signs if you don't want to anchor it to the beginning and/or end of the post, and remember that the content being searched is parsed HTML, *not* Markdown. This query will probably time out on SO unless you narrow down the date range! Try e.g. limiting the search to posts made within one or two years. On smaller sites, you can leave in the default values.

Stack Overflow

Q&A for programmers

-- PostHTML: HTML code to search for (T-SQL LIKE pattern, \ escapes)
-- ExcludeHTML: HTML code to exclude (T-SQL LIKE pattern, \ escapes)
-- StartDate: Earliest posting date (YYYY-MM-DD)
-- EndDate: Latest posting date (YYYY-MM-DD)
-- MaxResults: Maximum number of result rows

with Matched as (
  select * from Posts
  where Body like ##PostHTML:string?%search%## escape char(92)
    and Body not like ##ExcludeHTML:string?<nothing>## escape char(92)
)
select top ##MaxResults:int?100##
  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 Matched 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.CreationDate between ##StartDate:string?2000-01-01## and ##EndDate:string?2999-12-31## + ' 23:59:59'

Enter Parameters

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