MYSQL REGEXP for text not between parentheses

MySQLregular expression

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).

Best Answer

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

Related Solutions

Mysql – How to use the POSIX character classes upper and lower to search text in MySQL

From the article on mysql REGEXP:

REGEXP is not case sensitive, except when used with binary strings.

A simple solution would be to use the BINARY operator:

SELECT * FROM article_body WHERE body_text REGEXP BINARY "FREE";

As to why your example doesn't work, the way I interpret it is "Any string with one or more uppercase letter followed by FREE", but of course FREE is case-insensitive there.

Mysql – How to use SUBSTRING using REGEXP in MySQL

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string. The basic locate syntax you would need is explained here.

LOCATE (search str, str, [position])

search str = A string which will be searched for.

str =A string which is going to be searched.

position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here

SUBSTRING(str, pos, len)

str= A string.

pos= Starting position.

len= Length in characters.

The easier way of viewing this is to think of substring as the following SUBSTRING(str FROM pos FOR len)

The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.

declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)

Best Answer

Related Solutions

Mysql – How to use the POSIX character classes upper and lower to search text in MySQL

Mysql – How to use SUBSTRING using REGEXP in MySQL

Related Question