CODEWITHSUNDEEP
mysqlsqlregex
forestclown
forestclown

Reputation: 1602

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

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.

Upvotes: 3

Views: 445

Answers (1)

netblognet
netblognet

Reputation: 2026

Just try these one:

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

This matches "anything + Silver" or just Silver.

Upvotes: 1

Related Questions