Oracle PL/SQL Find Matching Braces/Brackets or any character

It is normally difficult to find matching braces while parsing.

The below code snippet will help you to find the matching braces.

This will save the sub_strings into a record type with start_pos and end_pos.


The logic is like this

  Find first open brace
  Loop till last open brace
    Find first close brace after this open brace
    Loop
      Count number of open braces and close braces between this open brace and close brace.
      If open count = close count -- The logic sits here
         Then we have found a matching brace substring.
      End if;
      Find next close brace after this open brace
    End Loop 

    Find Next Open Brace
  Loop End

declare

l_expression varchar2(1000) := q'# ( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) ) ( 7 ( 8 ) ( 9 ) ) #';

type pos_record is record (open_pos number, end_pos number, value varchar2(400));

type pos_tab_type is table of pos_record index by binary_integer;

l_pos_tab pos_tab_type;

l_index number := 1;

l_open_pos number;

l_end_pos number;

l_open_count number;

l_end_count number;

l_sub_expression varchar2(1000);

begin

l_open_pos := instr(l_expression, '(', 1);

while l_open_pos > 0 loop

l_pos_tab(l_index).open_pos := l_open_pos;

l_end_pos := instr(l_expression, ')', l_open_pos+1);

l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);

l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));

l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));

while l_open_count <> l_end_count loop

l_end_pos := instr(l_expression, ')', l_end_pos+1);

l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);

l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));

l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));

end loop;

l_pos_tab(l_index).end_pos := l_end_pos;

l_pos_tab(l_index).value := l_sub_expression;

l_open_pos := instr(l_expression, '(', l_open_pos+1);

l_index := l_index + 1;

end loop;

l_index := l_pos_tab.first;

while l_index is not null loop

dbms_output.put_line(l_index||'>'||l_pos_tab(l_index).open_pos||'-'||l_pos_tab(l_index).end_pos||':'||l_pos_tab(l_index).value);

l_index := l_pos_tab.next(l_index);

end loop;

end;

Output

-------

1>2-36:( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) )

2>6-22:( 2 ( 3 ) ( 4 ) )

3>10-14:( 3 )

4>16-20:( 4 )

5>24-28:( 5 )

6>30-34:( 6 )

7>38-54:( 7 ( 8 ) ( 9 ) )

8>42-46:( 8 )

9>48-52:( 9 )