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
> dnelson@stripped
