[sql] regex

[Robert Brenstein]

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

I am NOT sure the problem is with AND per se.

In first case, it matched
'roberto' and 'brenstein' to robert brenstein

seems to fail on right \b in first word but same works fine when matching
'robert' and 'brensteinson'

>Robert,
>
>Field u_mtch is the same from the same table?

yes u_mtch is in the same field in the same table

>Then this problem should be for simpler query with on FROM table, yes?
>
>Please check this.

but the results reported are in the second table

r_xxx fields are from registration1 and u_xxx from registration2b.

>May be second table give side effect.

Yes, if I reduce the query to a single table it works. But these 
tables are 1:1 related the traditional way (u_rid = r_rid).

>Let me know result

I just tried the suggestion from Brian to use indexed-by-words and 
non-regex search.

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 = 'robert' and u_mtch = 'brenstein'

This is a disaster: and's work like or's -- this returns all people 
that have firstname Robert regardless of what their lastname is.

Removing first table again "fixes" the problem.

Robert