How to extract group from regular expression in Oracle?

Question

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?

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