I have a string like this :

v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]'

and I need to separate sum:[] from where:[] and assign them to different variables like this:

v_sum_string:'sum(x)>1 and sum(y)>2 and sum(z)>3';
v_where_string:'n=1 and m=2';

What I've written so far is:

select regexp_substr('sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]',
from dual;

but it gives me error ORA-12726:unmatched bracket in regular expression and I can not understand where my problem is . I was wondering if you could help me with this and if you have better opinion about the original string (for example it would be easier to separate if it was parentheses) please say

thanks very much in advance

  • The error is because square brackets are part of regex syntax so you need to escape them with backslashes. If it's possible to use some other braces such as {} in the original string it might simplify things a bit. Jul 4, 2020 at 12:02

1 Answer 1


The error is because [ and ] are part of regex syntax, which makes handling them in a regex tricky as you have to escape them, e.g. 'sum:\[\]+,'

I found it easier to replace them on the fly with { and } before applying regexp_substr to the result:

with demo (str) as
      ( select 'sum:{sum(x)>1 and sum(y)>2 and sum(z)>3},where:{n=1 and m=2}'
        from dual )
select regexp_substr(translate(str,'[]','{}'),
         '(sum:{)([^{}]+)', 1, 1, 'i', 2) as sum_expr
     , regexp_substr(translate(str,'[]','{}'),
         '(where:{)([^{}]+)', 1, 1, '', 2) as where_expr
from   demo;
SUM_EXPR                            WHERE_EXPR
----------------------------------- -----------
sum(x)>1 and sum(y)>2 and sum(z)>3  n=1 and m=2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.