Thanks Dan, It never occured to me to use regular expressions. (I'm still new to Mysql)
You have opened up a whole new world to me :-) The tables I am working with only have a few thousand records in so performance is not a big issue and the reg expression works just fine (the response is near instant on my now aging P500 256mb PC) Thanks again. Clyde England *********** REPLY SEPARATOR *********** On 8/01/2003 at 11:45 PM Dan Nelson wrote: >In the last episode (Jan 09), Clyde England said: >> I have a database of names and would like to do a selection based on >> the number or words in a name >> >> eg the name "Peter Smith" has 2 words >> the name "Peter John Smith" has 3 words >> the name "Peter John Fred Smith" has 4 words >> >> IE I would like to select all names where there are 3 words in it for >> instance. If there were such a function as words(string) which >> returned the number of words in a string then the simple select >> syntax would be: >> >> select * from names where words(name) = 3 >> >> Of course in MySql there is no such function (that I am aware of) - >> so any ideas how I can achieve this result. > >Easy (although not all that fast) way: > >select * from names where name regexp "^[^ ]*( [^ ]*){2}$"; >+------------------+ >| name | >+------------------+ >| Peter John Smith | >+------------------+ > >The '2' in the regex is how many spaces are in the name. 0 = single >word, 1 = 2 words, etc. Exercise to the reader: make it work correctly >with runs of spaces, and handle tabs and other whitespace characters. > >Fast way: > >Write a UDF the implements your WORDS() function; this will be quite a >bit faster than the regex. > >Fastest way: > >Write the UDF, add another column to your table called `words`, index >it, and update it when you update your `name` field. Use that column >in your queries. > >-- > Dan Nelson > [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php