MySQL REGEXP and repeated words

Question

I have a specified problem.

I have, for example: Hello little, hello red, sometime and now.

I need regexp what will be match only if the clause contains both hello and red.

I have complete a regexp ((hello|red).*){2}()* what will work only if in clause one word hello and one word red, but in my clause two words hello and regexp will work without word red, what wrong:(

I need regexp what will match only if cause contain word hello and word red and not depend of repeat one of them.

Please help.

Thx.

Your regular expression works because hello is indeed found twice, you did not require red specifically to be there, but either hello or red. — PhistucK
– PhistucK, Commented May 5, 2014 at 6:07

Tim Pietzcker · Accepted Answer · 2014-05-05 06:08:55Z

4

Usually, one would use positive lookahead assertions for this task, but MySQL's regex engine doesn't support them.

Therefore, your only option (if you want to do this in a single regex) is to handle both variations (hello after red or hello before red) "manually":

hello.*red|red.*hello

For two "search words", that's probably acceptable - it doesn't scale nicely, though.

Your regex ((hello|red).*){2}()* is a bit strange; it means

(            # Start of group:
 (hello|red) # Match either hello or red
 .*          # Match any number of characters
){2}         # Match this group twice
()*          # Match the empty string any number of times...

so this matches hello foo hello or red bar red as well.

answered May 5, 2014 at 6:08

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

e.bezdomnikov Over a year ago

I think use what:)) Apparently no over way. Thx a lot.

Doro · Accepted Answer · 2014-05-05 06:16:33Z

2

I'm not sure if it'll solve your problem but here is my solution:

.*(hello.*red|red.*hello).*

Here is an online demo of it with complex explanation of how does it really works: http://regex101.com/r/eL3wA0

edited May 5, 2014 at 6:16

answered May 5, 2014 at 6:11

Doro

7958 silver badges25 bronze badges

Comments

Robin · Accepted Answer · 2014-05-05 06:48:08Z

0

It may depend on what you want to do, but I believe regex is an overkill for the task.

How about a simple:

WHERE col LIKE "%red%"
    AND col LIKE "%hello%"

answered May 5, 2014 at 6:48

Robin

9,7063 gold badges38 silver badges45 bronze badges

1 Comment

e.bezdomnikov Over a year ago

Unfortunately, for my task what not applicable. But thx any way.

Collectives™ on Stack Overflow

MySQL REGEXP and repeated words

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related