What is the REGEX for: FULLSTOP + no or not + chocolate

user_7734363 0
Viewed 21

I want to use RLIKE in MySQL to select cells that have the word "chocolate". However there must also be the word "no" or "not" between the word "chocolate" and the last fullstop that occurred before "chocolate".

In otherwirds what is the regular expresson for

.%chocolate

where % must contain 'not' or 'no' AND % must not contain another fullstop

For example all 3 of the following sentences are acceptable:

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.

The following 3 examples are rejected:

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.

Thank you.

mysql
1 Answers

I'm not sure about REGEX but this could be an alternative:

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;

Your 3 condition got me thinking so, I guess using SUBSTRING_INDEX() and HAVING to filter the first value after a single full-stop that have the word chocolate in it. Namely this:

SELECT val,
       SUBSTRING_INDEX(val,'.',2) AS valCheck
FROM mytable
HAVING valCheck LIKE '%chocolate%'

Will return the following:

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

Notice that valCheck returns exactly as it's original value because it's a match against the two conditions being placed. The next step is to make that as a subquery and filter the value with the 3rd condition; must have no or not after the full-stop but before the string chocolate. Here I'm using SUBSTRING_INDEX() again but this time with CASE expression and using the string chocolate as the delimiter.

Demo fiddle

user_109106920
Related