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]',
'sum:[]+,',
1,
1)
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
Best Answer
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 applyingregexp_substr
to the result: