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