如何从Oracle中的正则表达式中提取组?

I got this query and want to extract the value between the brackets.

我得到了这个查询，想要在括号之间提取值。

select de_desc, regexp_substr(de_desc, '\[(.+)\]', 1)
from DATABASE
where col_name like '[%]';

It however gives me the value with the brackets such as "[TEST]". I just want "TEST". How do I modify the query to get it?

但是它给了我括号中的值，比如“[TEST]”。我只是想要“测试”。如何修改查询以获取它?

2 个解决方案

#1

The third parameter of the REGEXP_SUBSTR function indicates the position in the target string (de_desc in your example) where you want to start searching. Assuming a match is found in the given portion of the string, it doesn't affect what is returned.

REGEXP_SUBSTR函数的第三个参数指示要开始搜索的目标字符串(示例中的de_desc)中的位置。假设在字符串的给定部分中找到了匹配，则不会影响返回的内容。

In Oracle 11g, there is a sixth parameter to the function, that I think is what you are trying to use, which indicates the capture group that you want returned. An example of proper use would be:

在Oracle 11g中，有一个函数的第6个参数，我认为这是您要使用的，它指示您希望返回的捕获组。适当使用的一个例子是:

SELECT regexp_substr('abc[def]ghi', '\[(.+)\]', 1,1,NULL,1) from dual;

Where the last parameter 1 indicate the number of the capture group you want returned.

其中最后一个参数1表示要返回的捕获组的数量。

10g does not appear to have this option, but in your case you can achieve the same result with:

10g似乎没有这个选项，但在您的情况下，您可以实现同样的结果:

select substr( match, 2, length(match)-2 ) from (
SELECT regexp_substr('abc[def]ghi', '\[(.+)\]') match FROM dual
);

since you know that a match will have exactly one excess character at the beginning and end. (Alternatively, you could use RTRIM and LTRIM to remove brackets from both ends of the result.)

因为你知道一场比赛的开始和结束都会有一个额外的角色。(或者，您可以使用RTRIM和LTRIM从结果的两端除去括号。)

#2

You need to do a replace and use a regex pattern that matches the whole string.

您需要执行替换并使用与整个字符串匹配的regex模式。

select regexp_replace(de_desc, '.*\[(.+)\].*', '\1') from DATABASE;

#1