Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    76
    Thanks
    61
    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

    Code:
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,381 Times in 4,346 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
    Code:
    col1 REGEXP '[[:<:]]buildings?[[:>:]]'
    The ? after the s makes that character optional.

    If you needed to make a group optional and with alternatives:
    Code:
    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.]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • 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
    •