By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
val TEXT
);
INSERT INTO mytable VALUES
('Charlie likes fruits. Charlie does not like chocolate.'),
('Today is a good day. I do not feel like eating chocolate or apples.'),
('1. No chocolate is allowed.'),
('Andy is not a child... He loves chocolate.'),
('Today is not a good day. I have no money. I want to eat chocolate and orange.'),
('1. Chocolate is good for you.');
SELECT * FROM mytable;
| val |
|---|
| Charlie likes fruits. Charlie does not like chocolate. |
| Today is a good day. I do not feel like eating chocolate or apples. |
| 1. No chocolate is allowed. |
| Andy is not a child... He loves chocolate. |
| Today is not a good day. I have no money. I want to eat chocolate and orange. |
| 1. Chocolate is good for you. |
SELECT val,
SUBSTRING_INDEX(val,'.',2) AS valCheck
FROM mytable
HAVING valCheck LIKE '%chocolate%'
| val | valCheck |
|---|---|
| Charlie likes fruits. Charlie does not like chocolate. | Charlie likes fruits. Charlie does not like chocolate |
| Today is a good day. I do not feel like eating chocolate or apples. | Today is a good day. I do not feel like eating chocolate or apples |
| 1. No chocolate is allowed. | 1. No chocolate is allowed |
| 1. Chocolate is good for you. | 1. Chocolate is good for you |
SELECT *
FROM
(SELECT val,
SUBSTRING_INDEX(val,'.',2) AS valCheck
FROM mytable
HAVING valCheck LIKE '%chocolate%') A
WHERE CASE WHEN SUBSTRING_INDEX(valCheck,'chocolate',1) LIKE '%no %' THEN 1
WHEN SUBSTRING_INDEX(valCheck,'chocolate',1) LIKE '%not %' THEN 1
ELSE 0 END=1;
| val | valCheck |
|---|---|
| Charlie likes fruits. Charlie does not like chocolate. | Charlie likes fruits. Charlie does not like chocolate |
| Today is a good day. I do not feel like eating chocolate or apples. | Today is a good day. I do not feel like eating chocolate or apples |
| 1. No chocolate is allowed. | 1. No chocolate is allowed |