从列中选择可能包含更多单词的第一个单词

Lets say I have a table simple as this:

让我说我有一个简单的表格如下:

id    |    name
1     |    one_word
2     |    two words
3     |    here we have four

So, I would like to get only rows containing one word, which in the above example would only be record with id 2.

所以,我想只获得包含一个单词的行,在上面的例子中,只有id为2的记录。

I did read the docs and tried various versions of this:

我确实阅读了文档并尝试了各种版本:

SELECT * FROM `table` WHERE name REGEXP '(.*?)\s';

so, please tell me where I'm doing it wrong.

所以,请告诉我,我做错了。

3 个解决方案

#1

If you are looking for the first word from a column that may have more words, you can use SUBSTRING_INDEX:

如果您要查找可能包含更多单词的列中的第一个单词,则可以使用SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX("here we have four", " ", 1)

to extract rows that don't have spaces, you can use for example this:

要提取没有空格的行,您可以使用例如:

name NOT REGEXP '\s'

名称NOT REGEXP'\ s'

name NOT LIKE '% %'

名字不喜欢'%%'

LOCATE(' ', name) = 0

LOCATE('',name)= 0

if your column can contain spaces at the beginning or at the end of the string, you could also use TRIM:

如果您的列可以在字符串的开头或结尾包含空格,您还可以使用TRIM:

SELECT SUBSTRING_INDEX(TRIM("  here we have four  "), " ", 1)

TRIM(name) NOT REGEXP '\s'

TRIM(名称)NOT REGEXP'\ s'

TRIM(name) NOT LIKE '% %'

TRIM(姓名)不喜欢'%%'

LOCATE(' ', TRIM(name)) = 0

LOCATE('',TRIM(name))= 0

#2

Thx to the comment from Strawberry, I came up with this:

对于Strawberry的评论,我想出了这个:

SELECT * FROM `table` WHERE name NOT REGEXP '\s'

which works great.

这很棒。

#3

What about this? In both LIKE '% %' and REGEXP case performance is bad. But i think LIKE has the least bad performance.

那这个呢?在LIKE'%%'和REGEXP案例中,表现都不好。但我认为LIKE表现最差。

SELECT * FROM `table` WHERE name NOT LIKE '% %'

#1