List:General Discussion« Previous MessageNext Message »
From:Abhi Sahay Date:January 9 2003 2:39pm
Subject:Re: Select * from names where words(name) = 3
View as plain text  
USE substring_index function
Example:
mysql> select substring_index(name,' ',3),name from test1;
+-----------------------------+-----------+
| substring_index(name,' ',3) | name      |
+-----------------------------+-----------+
| a b c                       | a b c d e |
| a b c                       | a b c     |
| a d c                       | a d c     |
| a d c                       | a d c e   |
+-----------------------------+-----------+
4 rows in set (0.00 sec)


Enjoy
Abhi


----- Original Message -----
From: "Clyde England" <clyde@stripped>
To: "Dan Nelson" <dnelson@stripped>
Cc: <mysql@stripped>
Sent: Thursday, January 09, 2003 1:03 AM
Subject: Re: Select * from names where words(name) = 3


> 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
>
>
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread129406@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-abhi=domainsystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

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