List:General Discussion« Previous MessageNext Message »
From:Clyde England Date:January 9 2003 6:03am
Subject:Re: Select * from names where words(name) = 3
View as plain text  
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



Thread
Select * from names where words(name) = 3Clyde England9 Jan
  • Re: Select * from names where words(name) = 3Dan Nelson9 Jan
    • Re: Select * from names where words(name) = 3Clyde England9 Jan
  • Re: Select * from names where words(name) = 3Abhi Sahay9 Jan