Would you help me to select a sentence by complex logic. Platform: TSQL.

Initial data:

conditions:

1. must have %Apple%
2. not take into account %"%Apple%"% This means: if the sentence has only %"%Apple%"%, condition not met But if the sentence has both %Apple% AND %"%Apple%"% - condition met

I tried to apply some kinds of logic:
First:

1. Substitute the word "Apple" with some rare symbol. Eg. "|"
2. Delete in the sentence all the symbols but | and quotes
3. To look for the "|" symbol and to look left and right from it. If the quote is absent on one of the sides, condition met.

Second:
Split the sentence on the basis of the word Apple

Third:
Split the sentence on the basis of the quotes

But I whether don't know how to technically fulfill the logic, or the logic doesn't meet the goal.

CodePudding user response：

Your sample data and explanation appears to just require the following, does this work for you?

with d as (
 select 'company "Apple corp" has an apple on its logotype  1' sentence union
 select 'company "Apple computers" is a large company   0' union
 select 'Apple company'
)
select * , case when Replace(sentence,'"apple','') like '%apple%' then 1 else 0 end
from d;

CodePudding user response：

If you really have to use sql, just use multiple conditions in your WHERE clause. This way, you don't have to call a function for replacements or other manipulations.

You can rephrase your conditions like this:

• Text contains only Apple but not "Apple"
  OR
• Text contains both Apple and "Apple"
  • Possibility 1: First apple, then "apple"
  • Possibility 2: First "apple", then apple

WHERE
(Col LIKE '%apple%' AND Col NOT LIKE '%"%apple%"%') -- APPLE, but not "APPLE"
OR Col LIKE '%apple%"%apple%"%'                     -- APPLE .. "APPLE" ..
OR Col LIKE '%"%apple%"%apple%'                     -- "APPLE" .. APPLE ..

db<>fiddle