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
