Oracle REGEXP_LIKE匹配非数字字符

I have a set of rows in my database with VARCHAR2 column values like so: tac903.2, tac903.24, tac903.2a, tac903.2b

我的数据库中有一组行，其VARCHAR2列值如下：tac903.2，tac903.24，tac903.2a，tac903.2b

I want to create a regex that does not match if the last character in the search expression is numeric. So when I search for "tac903.2" I want to only match: tac903.2, tac903.2a, and tac903.2b. This should be simple but I'm not getting the desired behaviour from my regex here:

如果搜索表达式中的最后一个字符是数字，我想创建一个不匹配的正则表达式。所以当我搜索“tac903.2”时，我想只匹配：tac903.2，tac903.2a和tac903.2b。这应该很简单，但我没有从我的正则表达式获得所需的行为：

REGEXP_LIKE(col, 'tac903.2[a-z]?'); //This is matching the tac903.24 record.

REGEXP_LIKE(col, 'tac903.2[^0-9]?'); //This also is matching the tac903.24 record.

I'm a beginner with regular expressions, but based on my research it seems like the above should achieve the behaviour I'm looking for. Can someone tell me what's wrong with them?

我是正则表达式的初学者，但根据我的研究，似乎上面应该实现我正在寻找的行为。有人能告诉我他们有什么问题吗？

2 个解决方案

#1

Set up sample data:

设置样本数据：

create table temp_matches (
  rec varchar2(10)
);

insert into temp_matches values ('tac903.2');
insert into temp_matches values ('tac903.2a');
insert into temp_matches values ('tac903.2b');
insert into temp_matches values ('tac903.24');
insert into temp_matches values ('2');
insert into temp_matches values ('a');

Query:

查询：

select *
from temp_matches
where REGEXP_LIKE(rec, '(.)*[^0-9]$');

Results:

结果：

tac903.2a
tac903.2b
a

The $ indicates the end of the line, so we just look for any values not ending in 0-9.

$表示该行的结尾，因此我们只查找不以0-9结尾的任何值。

#2

You can use word boundary [[:>:]] here.

您可以在此处使用单词边界[[：>：]]。

Something like this REGEXP_LIKE(col, 'tac903.2[a-z]?[[:>:]]');

像这样的东西REGEXP_LIKE（col，'tac903.2 [a-z]？[[：>：]]'）;

See demo.

见演示。

http://www.sqlfiddle.com/#!9/940fd/2/0

EDIT:

编辑：

`REGEXP_LIKE(col, 'tac903.2[a-z]?(\s|$|\W)');`

#1