Solved

# Replace function in oracle

Posted on 2009-04-29
406 Views
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

Regards

0
Question by:akp007

LVL 73

Expert Comment

why is the second case also 2 ?

which 2 of the 4 sets are you counting?
0

Author Comment

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

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
0

LVL 73

Expert Comment

so, you mean all nested parenthese are ignored for the purposes of counting.
0

LVL 18

Expert Comment

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,'[^()]') 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 );
/
0

LVL 73

Expert Comment

interesting approach but the model clause doesn't always return the right results...
``````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 );
``````
0

LVL 73

Accepted Solution

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(regexp_replace(str,'[^()]'),'\((\(\))*\)','(')) from x

0

LVL 18

Expert Comment

Yes. That works like a champ.
Good work SD.
0

Author Comment

thanks a lot. It's working. I am testing it and will get back to you

Thanks again
0

LVL 27

Assisted Solution

that may not work for nested paranthesis, say something like '(abcd)(abcd)(ab(cd(ef)gh)ij)'

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>
``````
0

LVL 73

Expert Comment

for nested parentheses it should work.  however it can give bad counts if the parentheses aren't even.

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.
0

Author Comment

I  need some clarification

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

Regards
0

LVL 73

Assisted Solution

replace

(\(\))*  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

0

LVL 73

Expert Comment

oops,  I just saw the example in sujith80's post.  don't know how I overlooked that.

yes, that's another hole.

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

LVL 73

Assisted Solution

however, you can expand the expression to any depth you want
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
``````
0

Author Closing Comment

Great and excellent solutions provided by all the experts. Thanks again for all your help

Best Regards
0

LVL 73

Expert Comment

0

## Featured Post

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dâ€¦
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-orâ€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.