which 2 of the 4 sets are you counting?

Solved

Posted on 2009-04-29

Dear experts -

I have to make a change to existing code. Here is the scenario. I need to count the number of paranthesis. Let me give you example

String :- (ABCEF1234 1234567 ), (XYZ123456 9999333 )

In the above example my Paranthesis count is going to be 2 ,

Now in the new scenario

the string will be like

( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))

In this case also , The number of paranthesis should be 2

Please suggest

Regards

I have to make a change to existing code. Here is the scenario. I need to count the number of paranthesis. Let me give you example

String :- (ABCEF1234 1234567 ), (XYZ123456 9999333 )

In the above example my Paranthesis count is going to be 2 ,

Now in the new scenario

the string will be like

( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))

In this case also , The number of paranthesis should be 2

Please suggest

Regards

16 Comments

In the above string, I put a comma ( but the strings may or may not be sepeated by comma).

so the set of paranthesis that I needed to consider for the above string are the ones adjacent to the comma

The above string can of the form

( ABCEF1234 1234567 (abc) and 3333333(xyz)), ( XYZ123456 9999333 (def))

then also the count is 2

thanks for the response

create table test as

select '(ABCEF1234 1234567 ), (XYZ123456 9999333 )' str_num from dual

union all

select '( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def)) ' str_num from dual ;

SELECT old_str_num, flag as balanced_parentheses

FROM

(

SELECT str_num as old_str_num, rownum rn,

regexp_replace(str_num,'[^

FROM test

)

MODEL

PARTITION BY (rn)

DIMENSION BY (0 dim)

MEASURES(old_str_num, str_num, CAST(NULL AS number) flag )

( flag[0] = length(regexp_replace(str_

/

```
with test as (
select '( ABCEF1234 1234567 (abc)), ( XYZ123456 9999333 (def))' str_num from dual union all
select '( ABCEF1234 1234567 (abc) and 3333333(xyz)), ( XYZ123456 9999333 (def))' from dual union all
select '(ABCEF1234 1234567 ), (XYZ123456 9999333 )' from dual union all
select '( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))' from dual
)
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 number) flag )
( flag[0] = length(regexp_replace(str_num[cv()], '\('))/2 );
```

with x as (

select '( ABCEF1234 1234567 (abc)), ( XYZ123456 9999333 (def))' str from dual union all

select '( ABCEF1234 1234567 (abc) and 3333333(xyz)), ( XYZ123456 9999333 (def))' from dual union all

select '(ABCEF1234 1234567 ), (XYZ123456 9999333 )' from dual union all

select '( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))' from dual

)

select str,length(regexp_replace(

try this function

```
SQL> create or replace function test_func(p_str varchar2)
2 return number
3 as
4 l_str varchar2(800) ;
5 l_count pls_integer := 0;
6 l_temp pls_integer := 0;
7 begin
8 l_str := regexp_replace(p_str, '[^\(\)]', '') ;
9 if l_str is null then return 0; end if;
10 for i in 1..length(l_str) loop
11 if substr(l_str,i,1) = '(' then
12 l_temp := l_temp + 1;
13 elsif substr(l_str,i,1) = ')' then
14 l_temp := l_temp - 1;
15 end if;
16 if l_temp = 0 then
17 l_count := l_count + 1;
18 end if;
19 end loop;
20 if l_temp <> 0 then
21 return l_count + 1;
22 else
23 return l_count;
24 end if;
25 end;
26 /
Function created.
SQL> select test_func(val), val from tbl1;
TEST_FUNC(VAL) VAL
-------------- -------------------------------------------------------------------------------
2 (ABCEF1234 1234567 ), (XYZ123456 9999333 )
2 ( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))
2 ( ABCEF1234 1234567 (abc) and 3333333(xyz)), ( XYZ123456 9999333 (def))
3 ()()((()))
0 test
3 (abcd)(abcd)(ab(cd(ef)gh)ij)
6 rows selected.
SQL>
```

for instance... " this is my string(((abc"

will return 3

but, if your parentheses are matched then it should work even if nested.

I'm willing to believe there might be cases I haven't considered, if you have some examples please post them and I'll try to correct.

the second reg_replace ==> '\((\(\))*\)','(' can you please explain. Solution is working for my scenario

Regards

(\(\))* means any number of ()

so

\((\(\))*\) means any number of () that are inside ()

so (()()()()()()()()()) will be replaced with (

using left parathenses wasn't significant, I just needed a place holder character for counting purposes

yes, that's another hole.

if the parentheses are nested more than 1 level deep my expression won't work

by nesting (\(\))* expressions

the following will handle up to 5 levels of nesting

```
with x as (
select '( ABCEF1234 1234567 (abc)), ( XYZ123456 9999333 (def))' str from dual union all
select '( ABCEF1234 1234567 (abc) and 3333333(xyz)), ( XYZ123456 9999333 (def))' from dual union all
select '(ABCEF1234 1234567 ), (XYZ123456 9999333 )' from dual union all
select '( ABCEF1234 1234567 (abc)) ( XYZ123456 9999333 (def))' from dual union all
select '(abcd)(abcd)(ab(cd(ef)gh)ij)' from dual
)
select str,length(regexp_replace(regexp_replace(str,'[^()]'),'\((\((\((\((\(\))*\))*\))*\))*\)','(')) cnt from x
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Becoming an Oracle Senior DBA | 5 | 71 | |

Unable to backup Oracle DBF file using RMAN due to bad block | 16 | 70 | |

surrogate key in database world | 6 | 60 | |

java.sql.SQLIntegrityConst |
4 | 31 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**12** Experts available now in Live!