Oracle使用REGEXP_LIKE标识多个空格

I'm trying to identify fields that have more than one space in a comment, e.g. 'this lhas three spaces'

我正在尝试识别评论中包含多个空格的字段，例如'这有三个空间'

Using this I can get anything with two spaces, but would like to be able to get 2 or more:

使用这个我可以获得两个空格的任何东西，但希望能够获得2个或更多：

select * from labtec.spaces
where REGEXP_LIKE(SPACES, '[[:space:]]{2}');

Any suggestions?

有什么建议么？

3 个解决方案

#1

where REGEXP_LIKE(SPACES, '[[:space:]][[:space:]]+');

#2

I believe that you can:

我相信你可以：

select * from labtec.spaces
where REGEXP_LIKE(SPACES, '[[:space:]]{2,}');

Note the comma.

注意逗号。

For "Between three and five" you would use {3,5}, for "two or more" {2,}, for "eight or less" {,8}

对于“在三到五之间”，您将使用{3,5}，“两个或更多”{2，}，用于“八个或更少”{，8}

#3

You do not need to check for two-or-more characters - checking for two is sufficient to filter the rows since if there are three characters then matching only two of them will work just as well as matching two-or-more.

您不需要检查两个或更多字符 - 检查两个字符就足以过滤行，因为如果有三个字符，那么仅匹配其中两个字符将与匹配两个或更多字符一样有效。

This will find strings which have two or more (consecutive or non-consecutive) space CHR(32) characters (without using regular expressions):

这将找到具有两个或更多（连续或非连续）空格CHR（32）字符的字符串（不使用正则表达式）：

SELECT *
FROM   labtec.spaces
WHERE  INSTR( spaces, ' ', 1, 2 ) > 0

This will find where there are two or more consecutive space CHR(32) characters:

这将找到有两个或更多连续空格CHR（32）字符的位置：

SELECT *
FROM   labtec.spaces
WHERE  INSTR( spaces, '  ' ) > 0

If you want any two (or more) consecutive white-space characters then you only need to check for two matching characters:

如果你想要任何两个（或更多）连续的空白字符，那么你只需要检查两个匹配的字符：

SELECT *
FROM   labtec.spaces
WHERE  REGEXP_LIKE( spaces, '\s\s' ) -- Or, using POSIX syntax '[[:space:]]{2}'

Update - Leading and trailing spaces

更新 - 前导和尾随空格

SELECT *
FROM   labtec.spaces
WHERE  SUBSTR( spaces, 1, 2 ) = '  ' -- at least two leading spaces
OR     SUBSTR( spaces, -2 )   = '  ' -- at least two trailing spaces

or, using (perl-like) regular expressions:

或者，使用（perl-like）正则表达式：

SELECT *
FROM   labtec.spaces
WHERE  REGEXP_LIKE( spaces, '^\s\s|\s\s$' )

#1

where REGEXP_LIKE(SPACES, '[[:space:]][[:space:]]+');

#2