Mysql regexp returning zero results

Question

Here is my query which does work:

select composition from `drugs` where composition regexp "^Diclofenac([^)])"

And the output:

Diclofenac(50mg),Thiocolchicoside(8mg)                                              
Diclofenac(50mg),Thiocolchicoside(4mg)                                              
Diclofenac(75mg),Thiocolchicoside(4mg)                                              
Diclofenac(50mg),Thiocolchicoside(4mg)                                              
Diclofenac(50mg),Tamsulosin(0.4mg)                                                  
Diclofenac(50mg),Eperisone(50mg)                                                    
Diclofenac(50mg),Thiocolchicoside(8mg)                                              
Diclofenac(100mg),Rabeprazole(20mg)                                                 
Diclofenac(75mg)                                                                    
Diclofenac(50mg),Thiocolchicoside(8mg)                                              
Diclofenac(50mg),Serratiopeptidase(10mg)                                            
Diclofenac(100mg),Eperisone(150mg)                                                  
Diclofenac(4mg),Thiocolchicoside(50mg)                                              
Diclofenac(100mg),Eperisone(150mg)                                                  
Diclofenac(50mg),Thiocolchicoside(4mg)                                              
Diclofenac(50mg),Thiocolchicoside(8mg)                                              
Diclofenac(50mg),Nimesulide(100mg)                                                  
Diclofenac(50mg),Paracetamol(325mg),Thiocolchicoside(8mg)                           
Diclofenac(NA),Serratiopeptidase(NA)                                                
Diclofenac(50mg),Nimesulide(100mg)                                                  
Diclofenac(50mg),Thiocolchicoside(4mg)                                              
Diclofenac(50mg),Paracetamol(325mg),Thiocolchicoside(4mg)                           
Diclofenac(100mg),Rabeprazole(20mg)                                                 
Diclofenac(50mg),Serratiopeptidase(10mg)                                            
Diclofenac(50mg),Nimesulide(100mg)

I want to search for drugs where composition begins with Diclofenac() followed by "," followed by Thiocolchicoside(). Tried following but gives zero results.

select composition from `drugs` where composition regexp "^Diclofenac([^)]),Thiocolchicoside([^)])"

Edit: Created DB fiddle: https://www.db-fiddle.com/f/fkQkn2WKdax2KbZqt7rPQ/0

Those parentheses probably need to be escaped with backslashes: select composition from drugs where composition regexp "^Diclofenac\([^)]\),Thiocolchicoside\([^)]\)" — Matt Gregory
– Matt Gregory, Commented Sep 9, 2017 at 5:05
[^)] merely says match any character, which is not ')'. Since you want to match multiple characters, you should say '[^)]+' . "+" means match 1 or more characters. — Salil
– Salil, Commented Sep 9, 2017 at 14:31

Salil · Accepted Answer · 2017-09-09 15:28:39Z

1

select composition from `drugs` where composition regexp "^Diclofenac\\([^)]+\\),Thiocolchicoside\\([^)]+\\)$"

Apart from adding '+' to match multiple non-right brace characters, i.e. [^)], you need to escape '(', because it has a special meaning in regular expressions

answered Sep 9, 2017 at 15:28

Salil

9,7529 gold badges45 silver badges59 bronze badges

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

Comments

Shota Noniashvili · Accepted Answer · 2017-09-09 05:13:38Z

0

Try this pattern ^Diclofenac(.*,Thiocolchicoside\(.*\))

.*, means that anything before ,

\(.*\) means (anything in brackets)

answered Sep 9, 2017 at 5:13

Shota Noniashvili

1099 bronze badges

1 Comment

kargirwar Over a year ago

I am assuming you meant ^Diclofenac(.*),Thiocolchicoside(.*). ".*" is greedy . It also selects following, for example: Diclofenac(50mg),Paracetamol(325mg),Thiocolchicoside(8mg)

Collectives™ on Stack Overflow

Mysql regexp returning zero results

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related