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

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, 07: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
company
companies
compan
[Okay, no such thing as "compan", but I couldn't thing of a better example.]