Re: string matching in mysql and php

On Thu, 14 Sep 2000, Anna Soobrattee wrote: > hi all, > > how can i search my database for a particular occurence of a string? > > select * from articles where content like "%anna%" - pulls out > everything containing the word anna and the substring anna, but i > would like to pull out article where the content contains the word > "anna" and not the substring anna. > > > had a look at the REGEXP funtion in the mysql documentation..but i > don't think i have got the syntax correct. > > Can anyone help ? Check out the '[[:<:]]' and '[[:>:]]' terms in the Mysql manual (appendix H, I think). They match the null string at word beginnings and endings, respecitively - so they match not only words in the middle of strings, but also at the beginning and end as well. And, of course, they also match if the word is the ONLY thing in the string. For example, your search would be: SELECT * FROM articles WHERE content REGEXP '[[:<:]]anna[[:>:]]' I'm pretty sure MySQL regexps are case-sensitive, so you might need to use something like: SELECT * FROM articles WHERE LOWER(content) REGEXP '[[:<:]]anna[[:>:]]' -steve ........................................................... . Steve Edberg sbedberg@ucdavis.edu . . Computer Consultant University of California, Davis . . (530)753-7605 (530)754-9127 . ...........................................................