Newbie REGEXP question

Hope it's OK for me to post a newbie question here.

(mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
readline 5.1)

I'd like to SELECT all Names that are composed of exactly two
"alphabetical" words: "John Smith" but not "John Elias Smith".

SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
{1}[[:alpha:]]+$';

This "almost" works but does also return some instances with more than
two names.

I would answer the same question if I could search for all Names in
which there was only one space. Similarly,

SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';

returns multiple names like "John Elias Smith".

Would you have the time to comment on this?

Best, JA

On 22 Feb, 12:38, jonas.anders...@rocketmail.com wrote:
> Hope it's OK for me to post a newbie question here.
>
> (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
> readline 5.1)
>
> I'd like to SELECT all Names that are composed of exactly two
> "alphabetical" words: "John Smith" but not "John Elias Smith".
>
> SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
> {1}[[:alpha:]]+$';
>
> This "almost" works but does also return some instances with more than
> two names.
>
> I would answer the same question if I could search for all Names in
> which there was only one space. Similarly,
>
> SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';
>
> returns multiple names like "John Elias Smith".
>
> Would you have the time to comment on this?
>
> Best, JA

For 2 names:
SELECT *
FROM `namestab`
WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+$'

For 3 names:
SELECT *
FROM `namestab`
WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+[[:space:]]+[A-Za-
z]+$'

On 22 Feb, 12:38, jonas.anders...@rocketmail.com wrote:
> Hope it's OK for me to post a newbie question here.
>
> (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
> readline 5.1)
>
> I'd like to SELECT all Names that are composed of exactly two
> "alphabetical" words: "John Smith" but not "John Elias Smith".
>
> SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
> {1}[[:alpha:]]+$';
>
> This "almost" works but does also return some instances with more than
> two names.
>
> I would answer the same question if I could search for all Names in
> which there was only one space. Similarly,
>
> SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';
>
> returns multiple names like "John Elias Smith".
>
> Would you have the time to comment on this?
>
> Best, JA

Also, in your one, you have simply missed off the ^ signifying the
beginning of the field thus:
SELECT Names FROM MyTable WHERE Names REGEXP '^[[:alpha:]]+[[:blank:]]
{1}[[:alpha:]]+$';

On Feb 22, 2:16 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 22 Feb 2007 04:38:54 -0800, jonas.anders...@rocketmail.com wrote:
>
> > Hope it's OK for me to post a newbie question here.
>
> > (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
> > readline 5.1)
>
> > I'd like to SELECT all Names that are composed of exactly two
> > "alphabetical" words: "John Smith" but not "John Elias Smith".
>
> > SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
> > {1}[[:alpha:]]+$';
>
> > This "almost" works but does also return some instances with more than
> > two names.
>
> It's finding the SECOND space in the column. You need to bind it at the
> beginning as well as the end, which you did with the '$' character.
>
> > I would answer the same question if I could search for all Names in
> > which there was only one space. Similarly,
>
> > SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';
> > returns multiple names like "John Elias Smith".
>
> > Would you have the time to comment on this?
>
> SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}' and
> Names NOT REGEXP '[[:blank:]]{1}.*[[:blank:]]{1}';
>
> Your version says "pick everything where there's a space in". This one
> says "pick everything where there's a space in and not two spaces in."
>
> --
> I love ASR, you have total freedom of speech as long as it's punctuated
> correctly. -- Chris Hacking in a.s.r

SELECT `names`
FROM `mytable`
WHERE TRIM( `names` ) LIKE "% %"
AND TRIM( `names` ) NOT LIKE "% % %";

> Would you have the time to comment on this?

Many thanks to all of you for helping me with this one! You guys are
great!

Best, JA

On 22 Feb, 15:22, jonas.anders...@rocketmail.com wrote:
> > Would you have the time to comment on this?
>
> Many thanks to all of you for helping me with this one! You guys are
> great!
>
> Best, JA

Aw shucks ... !blush!

On 22 Feb, 16:03, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 22 Feb 2007 06:05:37 -0800, Captain Paralytic wrote:
>
>
>
>
>
> > On 22 Feb, 12:38, jonas.anders...@rocketmail.com wrote:
> >> Hope it's OK for me to post a newbie question here.
>
> >> (mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using
> >> readline 5.1)
>
> >> I'd like to SELECT all Names that are composed of exactly two
> >> "alphabetical" words: "John Smith" but not "John Elias Smith".
>
> >> SELECT Names FROM MyTable WHERE Names REGEXP '[[:alpha:]]+[[:blank:]]
> >> {1}[[:alpha:]]+$';
>
> >> This "almost" works but does also return some instances with more than
> >> two names.
>
> >> I would answer the same question if I could search for all Names in
> >> which there was only one space. Similarly,
>
> >> SELECT Names FROM MyTable WHERE Names REGEXP '[[:blank:]]{1}';
>
> >> returns multiple names like "John Elias Smith".
>
> >> Would you have the time to comment on this?
>
> >> Best, JA
>
> > For 2 names:
> > SELECT *
> > FROM `namestab`
> > WHERE `names` REGEXP '^[A-Za-z]+[[:space:]]+[A-Za-z]+$'
>
> Are all letters in those [A-Za-z] ranges? áëôñ
>
> --
> For every subject you can think of there are at least 3 web sites.
> The owners of these web sites know each other and at least one of
> them hates at least one of the others.
> -- mnlooney's view of Skif's Internet Theorem- Hide quoted text -
>
> - Show quoted text -

No, but I doubt if any names will not contain at least one of them.