How to use MySQL regexp with 'and' 'or' in one statement

Question

I am using MySQL 5.x, and am trying to come out with a SQL statement to select rows base on the following datasets

ID | Type           | Name
1  | Silver         | Customer A
2  | Golden         | Customer B
3  | Silver, Golden | Customer C 
4  | Bronze, Silver | Customer D

I need to use regexp (Legacy system reasons) in the SQL statement, where I need to only select ID=1 and ID=4, which means I need "Silver", "Silver with Bronze" customer type, but not "Silver + Golden"

I am not very familiar with regular expressions, been trying with SQL like below:

SELECT DISTINCT `customer_type` FROM `customers` WHERE 
`customer_type` regexp 
"(Silver.*)(^[Golden].*)"

Where I need to have the regular expressions in one place like above, but not like below:

SELECT DISTINCT `customer_type` FROM `customers` WHERE 
`customer_type` regexp 
"(Silver.*)" 
AND NOT 
customer_type` regexp 
"(Golden.*)"

Although LIKE will work, but I can't use it for special reasons.

SELECT DISTINCT `customer_type` FROM `customers` WHERE 
`customer_type` LIKE "%Silver%" 
AND NOT 
customer_type` LIKE "%Golden%"

I couldn't get the first SQL statement to work, and not sure even if that is possible.

If you are testing for the exact string 'Silver', why not just do where customer_type = 'Silver'? — shree.pat18
– shree.pat18, Commented Jan 20, 2015 at 10:35
try this , "(Silver)(?!.*Golden]).*" if the sql support negative lookahead. — Avinash Raj
– Avinash Raj, Commented Jan 20, 2015 at 10:36
Avinash Raj, thanks, but I get #1139 - Got error 'repetition-operator operand invalid' from regexp — forestclown
– forestclown, Commented Jan 20, 2015 at 10:41
Use LIKE instead of REGEXP when the values are known (and exact), i.e "where Type like '%Silver%' and type not like '%Golden%¨'" — jarlh
– jarlh, Commented Jan 20, 2015 at 10:43

netblognet · Accepted Answer · 2015-01-20 14:09:28Z

1

Just try these one:

SELECT DISTINCT `id`, `customer_type` 
FROM `customers` 
WHERE `customer_type` regexp "^.*Silver$"

This matches "anything + Silver" or just Silver.

answered Jan 20, 2015 at 14:09

netblognet

2,0263 gold badges26 silver badges48 bronze badges

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

6 Comments

forestclown Over a year ago

Thanks for answer, but it doesn't take into account if we have Silver +?

netblognet Over a year ago

Ok, then I didn't got what you want. So let us work on a "ruleset", than I'll rewrite the query. So in my understanding there are the following rules: Allowed: "Silver", "Anything, Silver", "Silver, Anything". Not Allowed: "Silver, Gold". Are there other possible matches/rules?

forestclown Over a year ago

Almost there with the rules, but instead of Not allowed Silver, Gold, which should be Not allowed Gold.

forestclown Over a year ago

Basically following the rules like [ SELECT DISTINCT customer_type FROM customers WHERE customer_type LIKE "%Silver%" AND NOT customer_type` LIKE "%Golden%" ] but done in one regexp

forestclown Over a year ago

Not sure if it can be done in MySQL because MySQL doesn't support negative lookahead in Regexp

|

Collectives™ on Stack Overflow

How to use MySQL regexp with 'and' 'or' in one statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related