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