[sql] regex

[Lloyd Butler]

Robert,

I think you have been bitten by a known problem.

If you look at your sql statement, you are mixing the order in the WHERE
clause of tables.

You start off testing a condition of u_rid, then test r_rid, and then back
to u_mtch

You noted that once you removed the second condtion everything worked. That
is because the search on the table was not mixed up.

Try changing the order so that you check u_mtch, then u_rid and then r_rid.
This way you are testing on the u table, and the working to test on the r
table.

Your query should work.

Lloyd


> Date: Mon, 10 Feb 2003 14:09:06 +0100
> From: Robert Brenstein <rjb at rz.uni-potsdam.de>
> Subject: Re: [sql] regex
> To: Valentina Developers <valentina at lists.macserve.net>
> Message-ID: <a05200f02ba6d5420aaa7@[141.89.100.235]>
> Content-Type: text/plain; charset="us-ascii" ; format="flowed"
> 
> >on 2/10/03 6:16 AM, Robert Brenstein at rjb at rz.uni-potsdam.de wrote:
> >
> >>  Why the following sql
> >>
> >>  SELECT r_fna,r_mna,r_lna,r_ema,r_rid
> >>  FROM registration1,registration2b
> >  > WHERE u_rid = r_rid and r_rid > 0 and
> >>  u_mtch LIKE '\broberto\b' and u_mtch LIKE '\bbrenstein\b'
> >>
> >>  finds match for field containing "robert j brenstein"? robert is not
> >>  roberto, so it should fail in the first LIKE.
> >>
> >>  When I made the second LIKE non-matching, then the SQL fails as
> expected.
> >>
> >>  SELECT r_fna,r_mna,r_lna,r_ema,r_rid
> >>  FROM registration1,registration2b
> >>  WHERE u_rid = r_rid and r_rid > 0 and
> >>  u_mtch LIKE '\brobert\b' and u_mtch LIKE '\bbrensteinson\b'
> >>
> >>  I am confused. I want to do search on full words.
> >
> >Sounds like left LIKE works correctly, but problem in AND ?
> >I will check.
> >
> >Robert,
> >
> >Field u_mtch is the same from the same table?
> >Then this problem should be for simpler query with on FROM table, yes?
> >
> >Please check this.
> >
> >May be second table give side effect.
> >
> >Let me know result
> >
> 
> I just had an idea to remove the
> 
> and r_rid > 0
> 
> component and... all queries work fine for two tables. I mean both 
> regex and non-regex. Spooky.
> 
> Robert
> 
>