How to determine if the formula string contains balanced pairs of parentheses in SQL
June 8th, 2008 By Frank Zhou
The following SQL pattern can be used to determine whether the formula/expression string contains balanced pairs of parentheses.
create table test as select '( (1+2)*8 + ( (3+4)+(5+6) ) /9 ) * 9- (7+8)' str_num from dual union all select '( (1+2)+ (3+4) * 5 ) +6 ) ' str_num from dual ;
——————-SQL Solution —————-
SELECT old_str_num, flag as balanced_parentheses
FROM
(
SELECT str_num as old_str_num, rownum rn,
regexp_replace(str_num,'[^()]') str_num
FROM test
)
MODEL
PARTITION BY (rn)
DIMENSION BY (0 dim)
MEASURES(old_str_num, str_num, CAST(NULL AS VARCHAR2(1)) flag )
RULES ITERATE (10000) UNTIL (str_num[0] IS NULL OR flag[0] = 'F')
(
flag[0] = CASE WHEN regexp_like(str_num[cv()],'^\(.*\)$')AND
(length(regexp_replace(str_num[cv()], '\(')) =
length(regexp_replace(str_num[cv()], '\)'))
)
THEN 'T'
ELSE 'F' END,
str_num[0] = CASE WHEN flag[0] = 'T'
THEN regexp_replace(str_num[cv()], '\(\)')
END
);
OLD_STR_NUM BALANCED_PARENTHESES
-------------------------------------------- -------------------------
( (1+2)*8 + ( (3+4)+(5+6) ) /9 ) * 9- (7+8) T
( (1+2)+ (3+4) * 5 ) +6 ) F
June 9th, 2008 at 4:27 pm
Or a simple
select str_num,
case when length(replace(str_num,’)')) = length(replace(str_num,’('))
then ‘T’ else ‘F’ end bal
from test
June 10th, 2008 at 7:20 am
Gmyers,
Your simpler method doesn’t work,
it didn’t put all cases into consideration.
For example when str_num = ‘1+)2+(3+4′
SQL> select str_num,
2 case when length(replace(str_num,’)')) = length(replace(str_num,’('))
3 then ‘T’ else ‘F’ end bal
4 from ( select ‘1+)2+(3+4′ str_num from dual);
STR_NUM BAL
——— —–
1+)2+(3+4 T
The correct answer should be “F”.
Frank