I'm having trouble trying to figure out some MySQL regexp. Say I'm
searching for any one of 3 terms.
I know I can use

select id,desc from db where desc regexp "red|white|blue";

This works fine but what if we wanted to be able to match ALL of those
terms but not in any particular
order? The 6 potential orders is what is messing me up

red white blue
red blue white
white red blue
etc.

I do not want to use a full text search due to using InnoDB tables
and foreign keys.
Is there a regexp or am I limited to using multiple like conditions
which could really be a pain not to mention
inefficient?

Cheers,
validus

== Quote from validus (validus1@gmail.com)'s article
> I'm having trouble trying to figure out some MySQL regexp. Say I'm
> searching for any one of 3 terms.
> I know I can use
> select id,desc from db where desc regexp "red|white|blue";
> This works fine but what if we wanted to be able to match ALL of those
> terms but not in any particular
> order? The 6 potential orders is what is messing me up
> red white blue
> red blue white
> white red blue
> etc.
> I do not want to use a full text search due to using InnoDB tables
> and foreign keys.
> Is there a regexp or am I limited to using multiple like conditions
> which could really be a pain not to mention
> inefficient?
> Cheers,
> validus

i guess one thing you can do is:
select id,desc from db where desc regexp "red|white|blue" or regexp
"red|blue|white" or desc regexp "white|blue|red" (and all the rest of them);

--
POST BY: lark with PHP News Reader

validus wrote:
> I'm having trouble trying to figure out some MySQL regexp. Say I'm
> searching for any one of 3 terms.
> I know I can use
>
> select id,desc from db where desc regexp "red|white|blue";
>
> This works fine but what if we wanted to be able to match ALL of those
> terms but not in any particular
> order? The 6 potential orders is what is messing me up
>
> red white blue
> red blue white
> white red blue
> etc.
>
> I do not want to use a full text search due to using InnoDB tables
> and foreign keys.
> Is there a regexp or am I limited to using multiple like conditions
> which could really be a pain not to mention
> inefficient?
>
> Cheers,
> validus

Are you suggesting that a desc field could contain all 3 colours in a single
record?

Yes. Of course the real application isn't searching for colors. That
was just a quick example.
Sorry for the confusion. Basically I'm trying to find the easiest and/
or the most efficent method
to show rows with ALL 3 terms in any given order without a full text
search if possible.
Of course that may be the only decent method to implement this. I
wasn't sure if there
was any regexp mojo that could be done to implement this.

validus


> Are you suggesting that a desc field could contain all 3 colours in a single
> record?- Hide quoted text -
>
> - Show quoted text -

validus wrote:
> Yes. Of course the real application isn't searching for colors. That
> was just a quick example.
> Sorry for the confusion. Basically I'm trying to find the easiest
> and/ or the most efficent method
> to show rows with ALL 3 terms in any given order without a full text
> search if possible.
> Of course that may be the only decent method to implement this. I
> wasn't sure if there
> was any regexp mojo that could be done to implement this.
>
> validus
>
>
>> Are you suggesting that a desc field could contain all 3 colours in
>> a single record?- Hide quoted text -
>>
>> - Show quoted text -

Rows with 3 terms where? If you have 3 different "terms" in a single field,
then your database isn't even in 1NF. Normalise it and the problem goes
away.

Let me try to clarify some more. This is a description field with a
column type of text.
It's purpose is to store a description about something. If we were
talking about
products, it could be the product description.

Imagine we had a camera product we were storing. The desc might be
something
like:
================================================== ===========
Example Description Text

This new Sony camera provides 5 megapixel resolution for big prints
and photo cropping,
large 2.5-inch LCD screen, advanced optics and the appeal of
traditional
rangefinder shooting--so photo enthusiasts will have a camera thats
equally
adept at saving their memories and capturing their vision. It also
features a
3x optical zoom.
================================================== ===========
The description is descriptive text about what we are modeling.
You can't normalize a description like this out as they
will very from item to item at least in our application and there is
only one
description per item. I understand that many product types have
specification
lists and that can be normalized out to other tables and displayed
along with
description but that is NOT what we are talking about here.

When I said "terms" maybe I used the wrong word. Perhaps keywords
would
have been better. Using the above example, say the user wanted to
show
products where the description contained the keywords Sony, optical,
and new
but we wouldn't want to consider order as the user doesn't care what
order
they appear in but wants to see only the products that mention all 3
of these
things in the description.

Thanks for the input.

validus

> Rows with 3 terms where? If you have 3 different "terms" in a single field,
> then your database isn't even in 1NF. Normalise it and the problem goes
> away.- Hide quoted text -
>
> - Show quoted text -

On 1 Oct, 23:41, validus <valid...@gmail.com> wrote:
> Let me try to clarify some more. This is a description field with a
> column type of text.
> It's purpose is to store a description about something. If we were
> talking about
> products, it could be the product description.
>
> Imagine we had a camera product we were storing. The desc might be
> something
> like:
> ================================================== ===========
> Example Description Text
>
> This new Sony camera provides 5 megapixel resolution for big prints
> and photo cropping,
> large 2.5-inch LCD screen, advanced optics and the appeal of
> traditional
> rangefinder shooting--so photo enthusiasts will have a camera thats
> equally
> adept at saving their memories and capturing their vision. It also
> features a
> 3x optical zoom.
> ================================================== ===========
> The description is descriptive text about what we are modeling.
> You can't normalize a description like this out as they
> will very from item to item at least in our application and there is
> only one
> description per item. I understand that many product types have
> specification
> lists and that can be normalized out to other tables and displayed
> along with
> description but that is NOT what we are talking about here.
>
> When I said "terms" maybe I used the wrong word. Perhaps keywords
> would
> have been better. Using the above example, say the user wanted to
> show
> products where the description contained the keywords Sony, optical,
> and new
> but we wouldn't want to consider order as the user doesn't care what
> order
> they appear in but wants to see only the products that mention all 3
> of these
> things in the description.
>
> Thanks for the input.
>
> validus
> - Show quoted text -

Would have saved a lot of time if you'd explained this or used more
deswcriptive examples at the start!

In the absence of FULLTEXT, how about:

SELECT
id,
desc
FROM db
WHERE desc LIKE '%red%' AND desc LIKE '%white%' AND desc LIKE '%blue%'

Sorry. Didn't realize it wasn't clear at the beginning and was trying
to be as brief as possible.
Thanks for the suggestions using LIKE. I was aware of those but was
wondering if there was
a REGEXP solution that might be cleaner/faster. Of course there may
not be one.

cheers

> Would have saved a lot of time if you'd explained this or used more
> deswcriptive examples at the start!
>
> In the absence of FULLTEXT, how about:
>
> SELECT
> id,
> desc
> FROM db
> WHERE desc LIKE '%red%' AND desc LIKE '%white%' AND desc LIKE '%blue%'- Hide quoted text -
>
> - Show quoted text -

On 2 Oct, 15:22, validus <valid...@gmail.com> wrote:
> Sorry. Didn't realize it wasn't clear at the beginning and was trying
> to be as brief as possible.
> Thanks for the suggestions using LIKE. I was aware of those but was
> wondering if there was
> a REGEXP solution that might be cleaner/faster. Of course there may
> not be one.
>
> cheers
>
>
>
> > Would have saved a lot of time if you'd explained this or used more
> > deswcriptive examples at the start!
>
> > In the absence of FULLTEXT, how about:
>
> > SELECT
> > id,
> > desc
> > FROM db
> > WHERE desc LIKE '%red%' AND desc LIKE '%white%' AND desc LIKE '%blue%'- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Well, I'm not sure if MySQL supports this part of the regex spec, but
this works for some implementations:

(?=.*red)(?=.*white)(?=.*blue)

or to find only complete words:

(?=.*\bred\b)(?=.*\bwhite\b)(?=.*\bblue\b)

That is more along the lines of what I was trying to figure out. Not
sure
if MySQL supports that yet but it is a good place to start. Thanks
for the
assist all.

> Well, I'm not sure if MySQL supports this part of the regex spec, but
> this works for some implementations:
>
> (?=.*red)(?=.*white)(?=.*blue)
>
> or to find only complete words:
>
> (?=.*\bred\b)(?=.*\bwhite\b)(?=.*\bblue\b)- Hide quoted text -
>
> - Show quoted text -