filter SE posts with specific keywords

Question

I am trying to find all the SE answers which contain "on context" in a single sentence, in the meanwhile the "on" is not a part of common collocations in any form, such as "depending on context", "depend on context", "Based on context", "rely on context", "Counting on Context", "Count on Context" ...

Here is the code

SELECT Id, CreationDate, Score, Body
INTO #tb_postcount
  FROM Posts
  WHERE Body LIKE '% on context%'
    AND PostTypeId=2

SELECT Id AS [Post Link] from #tb_postcount
WHERE Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%depend%on context%'
and Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%base%on context%'
and Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%rely%on context%'
and Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%count%on context%'
and Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%lean%on context%'
and Body collate SQL_Latin1_General_CP1_CI_AS NOT LIKE '%take%on context%'

It works well, at least to me:) Could someone please take a look at it?

Is there a more efficient way to do the job?

Answer 1

The following are a few suggestions on how I'd write the SQL statement.

Source Control

If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.

Formatting

I would download the following tool for SSMS and Visual Studio, SQL formatter from ApexSQL. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.

Copy & Paste

If you find yourself copying and pasting the same string or number over and over in your query, then you should define it as a variable or create a table. Copy and paste is a design error ~ David Parnas

Commas

I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.

Where Clause

If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference

Common Table Expressions (CTE)

CTE's in your SQL, help with documentation. The expression name can then let other developers know why you used that expression e.g. post_body_filter.

Schema Names

Always reference the schema when selecting an object e.g. [dbo].[Posts].

Keywords

Avoid using keywords as object names. Microsoft Reference

• Also check out the book Clean Code. It will change the way you think about naming conventions.

---

Revised SQL

Without table definitions and sample records I was unable to test this, but it should give you a good start.

WITH
Posts
AS
(
   SELECT tbl.* FROM (VALUES
    ( 1, '01-Jan-2020', 85, 'depend on context', 2)
   , ( 2, '02-Jan-2020', 86, 'base on Context', 2)
   , ( 3, '03-Jan-2020', 87, 'rely on context', 2)
   , ( 4, '04-Jan-2020', 88, 'Count on Context', 2)
   , ( 5, '05-Jan-2020', 89, 'lean on context', 2)
   , ( 6, '06-Jan-2020', 90, 'Take on Context', 2)
   , ( 7, '07-Jan-2020', 91, '... on context', 2)
   , ( 8, '08-Jan-2020', 92, ' on context ...', 2)
   , ( 9, '09-Jan-2020', 93, '... on context ...', 2)
   , ( 10, '10-Jan-2020', 94, '... on ... context ...', 2)
   , ( 11, '11-Jan-2020', 95, '...', 2)
   , ( 12, '12-Jan-2020', 96, '... on context ...', 1)
   ) tbl ([Id], [CreationDate], [Score], [Body], [PostTypeId]) 
)
,
post_body_filter --here it would be good to name the table expression so others will know why you're filtering them out.
AS
(
   SELECT tbl.* FROM (VALUES
    ( '%depend%')
   , ( '%base%')
   , ( '%rely%')
   , ( '%count%')
   , ( '%lean%')
   , ( '%take%')
   ) tbl ([Body]) 
)
SELECT 
     [po].[Id]
    , [po].[CreationDate]
    , [po].[Score]
    , [po].[Body]
FROM
    [Posts] AS [po]
    LEFT JOIN [post_body_filter] AS [pf] ON [po].[Body] LIKE [pf].[Body] COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
  1 = 1
  AND [pf].[Body] IS NULL
  AND [po].[Body] LIKE '% on context%'
  AND [po].[PostTypeId] = 2
  ;

Results