0

I'm looking to search a large MYSQL table for a string and only return results that aren't between parentheses. If my table looks like this:

|  ID   |Data                      |
|   1   |Some search term here     |
|   2   |Search term               |
|   3   |Search term (search term) |
|   4   |(Search term) search term |
|   5   |Here's the (search term)  |
|   6   |Never match this line     |

And I want to select the rows where "search term" (case-insensitive) appears, but not in parentheses, how can I write the REGEXP to achieve that?

IDs 1,2,3,4 should return. IDs 5 & 6 shouldn't.

The search term may include letters, numbers and punctuation (though it shouldn't need to support parentheses as they'll never be nested).

1

I hate regex(or dont get them) so:

SELECT id,data FROM table 
WHERE CONCAT(REPLACE(SUBSTRING_INDEX(data,'(',1),'(',''),
REPLACE(SUBSTRING_INDEX(data,')',-1),')','')) 
LIKE '%search term%';

SQL Fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.