问题描述
我得到了这个查询,想提取方括号之间的值.
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 '[%]';
但是,它为我提供了带有方括号"[TEST]"的值.我只想要测试".如何修改查询以获取它?
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?
推荐答案
REGEXP_SUBSTR函数的第三个参数指示您要开始搜索的目标字符串(在您的示例中为de_desc
)中的位置.假设在字符串的给定部分找到了匹配项,则不会影响返回的内容.
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.
在Oracle 11g中,该函数有第六个参数,我认为这是您要使用的参数,它指示您要返回的捕获组.正确使用的示例如下:
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:
SELECT regexp_substr('abc[def]ghi', '\[(.+)\]', 1,1,NULL,1) from dual;
最后一个参数1
表示要返回的捕获组的编号.这是一个链接,该文档描述了参数.
Where the last parameter 1
indicate the number of the capture group you want returned. Here is a link to the documentation that describes the parameter.
10g似乎没有此选项,但是您可以通过以下方法获得相同的结果:
10g does not appear to have this option, but in your case you can achieve the same result with:
select substr( match, 2, length(match)-2 ) from (
SELECT regexp_substr('abc[def]ghi', '\[(.+)\]') match FROM dual
);
因为您知道比赛在开始和结束时都会有一个多余的字符. (或者,您可以使用RTRIM和LTRIM从结果的两端删除方括号.)
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.)
这篇关于如何从Oracle中的正则表达式中提取组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!