Mysql regex match names with a maximum of two words

i've being trying this without succe开发者_StackOverflow社区ss:

select * from table where name regexp '^[:alpha:]{2}$'

pls help me?

There probably needs to be some white space in between the two words, right? Try

select * from table where name regexp '^[[:alpha:]]+[[:space:]]*[[:alpha:]]*$'

• [[:alpha:]]+ matches one or more letter characters
• [[:space:]]* matches zero or more whitespace characters. (You may want to use [[:blank:]]* instead, to only match spaces and tabs, or [[ ]]* for spaces only.)
• [[:alpha:]]* matches zero or more letter characters

So this should accept strings like

• "foo"
• "foo "
• "foo "
• "foo bar"
• "foo bar"

and reject strings like

• " foo"
• " foo "
• "foo bar baz"

select * from table where name regexp '^[:alpha:][:blank:]^[:alpha:]*$'