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

x
?
Solved

Replace function in oracle

Posted on 2009-04-29
16
Medium Priority
?
417 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:akp007
  • 9
  • 4
  • 2
  • +1
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24265899
why is the second case also 2 ?

which 2 of the 4 sets are you counting?
0
 

Author Comment

by:akp007
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

by:sdstuber
ID: 24265978
so, you mean all nested parenthese are ignored for the purposes of counting.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 18

Expert Comment

by:sventhan
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

by:sdstuber
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 );

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 24266227
how about something like this?




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

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

Author Comment

by:akp007
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

by:sujith80
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> 

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
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

by:akp007
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

by:sdstuber
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

by:sdstuber
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

by:sdstuber
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

Open in new window

0
 

Author Closing Comment

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

Best Regards
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24297907
glad we could help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

834 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question