Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help needed with INSTR

Re: Help needed with INSTR

From: Ed Prochak <edprochak_at_gmail.com>
Date: 11 Apr 2007 12:50:16 -0700
Message-ID: <1176321016.799773.37390@e65g2000hsc.googlegroups.com>


On Apr 11, 10:56 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> Sandy80 wrote:
> > Hi,
>
> > I have a field which has some values which have a paranthesis '(' in
> > them. If there is any such value I want only the data before it. That
> > means if its "PMQ Manager (w/ direct reports)" I only want the value
> > "PMQ Manager".
> > So I am able to get that value by using:
>
> > substr(asg.POSITION_TITLE_DSC,0,INSTR(asg.POSITION_TITLE_DSC, '(')-1)
>
> > But the problem is that it gets the values only with a '(' in them.
> > Using a in built PL/SQL function how do I also get the values which do
> > not have a '(' in them?
>
> > Any help?
>
> > Regards,
> > Sandy
>
> SQL> create table test (colx varchar2(50));
>
> Table created.
>
> SQL> insert into test values ('PMQ Manager (w/ direct reports)');
>
> 1 row created.
>
> SQL> insert into test values ('PMQ Manager w/o parens');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from test;
>
> COLX
> --------------------------------------------------
> PMQ Manager (w/ direct reports)
> PMQ Manager w/o parens
>
> Two rows in the table...one with parentheses and one without. Querying
> similar to your post shows that the second row is not returned.
>
> SQL> select substr(colx,1,instr(colx,'(')-1) from test;
>
> SUBSTR(COLX,1,INSTR(COLX,'(')-1)
> --------------------------------------------------
> PMQ Manager
>
> The reason lies in the value returned from INSTR when the search string
> is not found:
>
> SQL> select colx,instr(colx,'(') from test;
>
> COLX INSTR(COLX,'(')
> -------------------------------------------------- ---------------
> PMQ Manager (w/ direct reports) 13
> PMQ Manager w/o parens 0
>
> You can see without the search string, INSTR returns 0. And then your
> SQL statement subtracts one from that. The SQL statement then says to
> get the first through the -1 character of the column. That isn't
> possible. So you need to test for the presence of the search string. If
> absent, just return the column. If present, return the substring of the
> column. To test this presence, you can use DECODE similar to the following:
>
> SQL> select decode(instr(colx,'('),0,colx,substr(colx,1,instr(colx,'(')-1))
> 2 from test;
>
> DECODE(INSTR(COLX,'('),0,COLX,SUBSTR(COLX,1,INSTR(
> --------------------------------------------------
> PMQ Manager
> PMQ Manager w/o parens
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

that seemed like a long solution to me. My improvement reduces the number of function calls:

SQL> select nvl( substr(colx,1,instr(colx,'(')-1),colx) from test;

NVL(SUBSTR(COLX,1,INSTR(COLX,'(')-1),COLX)



PMQ Manager
PMQ Manager w/o parens

SQL> (my original approach was to put the decode() inside the substr() on the instr(), but that meant call instr() twice. It was just unpleasent to me.)

Similar to PERL, Oracle usually has more than one solution.   Ed Received on Wed Apr 11 2007 - 14:50:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US