How to extract group from regular expression in 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?

Solution 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.

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;

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 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
);

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.)

Solution 2:

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

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