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" <[EMAIL PROTECTED]>
To: "Dan Nelson" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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
> > [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
>
---------------------------------------------------------------------
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
