SQL 如何从Oracle中的正则表达式中提取组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7758859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract group from regular expression in Oracle?
提问by Henley Chiu
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]”。我只想要“测试”。如何修改查询以获取它？
回答by Dave Costa
The third parameter of the REGEXP_SUBSTR function indicates the position in the target string (de_descin 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 中，该函数有第六个参数，我认为这就是您要使用的参数，它表示要返回的捕获组。正确使用的一个例子是：
SELECT regexp_substr('abc[def]ghi', '\[(.+)\]', 1,1,NULL,1) from dual;
Where the last parameter 1indicate the number of the capture group you want returned. Here is a linkto the documentation that describes the parameter.
其中最后一个参数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 从结果的两端删除括号。）
回答by ivanatpr
You need to do a replace and use a regex pattern that matches the whole string.
您需要进行替换并使用匹配整个字符串的正则表达式模式。
select regexp_replace(de_desc, '.*\[(.+)\].*', '') from DATABASE;
