OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

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

3 Responses to “How to determine if the formula string contains balanced pairs of parentheses in SQL”

  1. gmyers Says:

    Or a simple
    select str_num,
    case when length(replace(str_num,’)’)) = length(replace(str_num,’(‘))
    then ‘T’ else ‘F’ end bal
    from test

  2. Frank Zhou Says:

    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

  3. newkid Says:
    WITH t AS (
    SELECT str_num, REGEXP_REPLACE(str_num,'[^()]') AS str FROM test
    )
    SELECT str_num
          ,CASE WHEN MAX(left_cnt) = MAX(right_cnt) AND SUM(CASE WHEN right_cnt>left_cnt THEN 1 ELSE 0 END)=0
                THEN 'T'
                ELSE 'F'
           END AS flag
      FROM ( SELECT str_num,rn
                   ,COUNT(CASE WHEN c='(' THEN 1 END) OVER(PARTITION BY str_num ORDER BY rn) left_cnt
                   ,COUNT(CASE WHEN c=')' THEN 1 END) OVER(PARTITION BY str_num ORDER BY rn) right_cnt
               FROM (SELECT str_num,str,SUBSTR(str,rn,1) c,rn,LENGTH(str) l
                      FROM t,(SELECT ROWNUM rn FROM (SELECT MAX(LENGTH(str)) AS m FROM t) CONNECT BY ROWNUM<=m)
                     WHERE rn<=LENGTH(str)
                     )
            )
    GROUP BY str_num
    ;
    

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question