[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Replace function in oracle

Posted on 2009-04-29
Medium Priority
417 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
• 9
• 4
• 2
• +1

LVL 74

Expert Comment

ID: 24265899
why is the second case also 2 ?

which 2 of the 4 sets are you counting?
0

Author Comment

ID: 24265953
( 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 74

Expert Comment

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

LVL 18

Expert Comment

ID: 24266197

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 74

Expert Comment

ID: 24266214
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 74

Accepted Solution

sdstuber earned 1600 total points
ID: 24266227

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

ID: 24266340
Yes. That works like a champ.
Good work SD.
0

Author Comment

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

Thanks again
0

LVL 27

Assisted Solution

sujith80 earned 400 total points
ID: 24267252
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 74

Expert Comment

ID: 24269645
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

ID: 24271487
I  need some clarification

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

Regards
0

LVL 74

Assisted Solution

sdstuber earned 1600 total points
ID: 24271714
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 74

Expert Comment

ID: 24271737
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 74

Assisted Solution

sdstuber earned 1600 total points
ID: 24271764
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

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

Best Regards
0

LVL 74

Expert Comment

ID: 24297907
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates? Â They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", andâ€¦
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
###### Suggested Courses
Course of the Month19 days, 2 hours left to enroll