On Fri, 1999-08-13 22:20:24 -0700,
Jerry Preeper <preeper@stripped> wrote:
> Thimble Smith <tim@stripped> writes:
> > where directory regexp '[[:<:]]dir[[:>:]]'
> That didn't work. I think it's because my phrases are not considered words
> since they have a / in them.
SELECT * FROM users WHERE directory REGEXP '(^| )dir( |$)';
(^| ) at beginning or space before it
( |$) space behind it or at end
Another possibility is to work with string functions:
SELECT * FROM users
WHERE FIND_IN_SET('dir', REPLACE(directory, ' ', ','))>0;
Though this looks more complex than the first version with REGEXP,
my test shows that it's much faster:
FIND_IN_SET('dir', REPLACE(directory, ' ', ','))>0)
--> 2.33 sec with my test data.
directory REGEXP '(^| )dir( |$)')
--> 28.41 sec with my test data.
So at least for MySQL V3.22.19b on Solaris 2.6 REGEXP seems to be
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7