View Full Version : Shortening my query?

02-28-2013, 03:56 PM
Hello, I'm looking to run a query which runs a regular expression query on three columns searching for a term and the plural variety on the term. So if I was looking for the word building my search would look like this

SELECT col1,col2,col3 FROM `table`
WHERE col1 REGEXP '[[:<:]]building[[:>:]]'
OR col2 REGEXP '[[:<:]]building[[:>:]]'
OR col3 REGEXP '[[:<:]]building[[:>:]]'
OR col1 REGEXP '[[:<:]]buildings[[:>:]]'
OR col2 REGEXP '[[:<:]]buildings[[:>:]]'
OR col3 REGEXP '[[:<:]]buildings[[:>:]]'

Now as you can see my query is quite long and if I wanted to add additional columns to search columns 4,5 & 6 it would get longer still. So my question is please there anyway I could shorten this type of query to make it faster or is this the best way?

Thank you

Old Pedant
02-28-2013, 08:19 PM
For starters, why the regexp???

It would be much more efficient to use LIKE if you don't really need the regexp.

But if you do use the regexp, what's wrong with simply

col1 REGEXP '[[:<:]]buildings?[[:>:]]'

The ? after the s makes that character optional.

If you needed to make a group optional and with alternatives:

col1 REGEXP '[[:<:]]compan(y|ies)?[[:>:]]'

which would match
[Okay, no such thing as "compan", but I couldn't thing of a better example.]