Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Sep 2012
    Thanked 0 Times in 0 Posts

    Shortening my query?

    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

  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    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.]
    Be yourself. No one else is as qualified.

  3. Users who have thanked Old Pedant for this post:

    Oatley (03-01-2013)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts