Re: Help needed with INSTR

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 11 Apr 2007 09:56:38 -0500
Message-ID: <461ceacb$0$16283$88260bb3@free.teranews.com>

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
dba_at_nospam.peasland.net
http://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

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Wed Apr 11 2007 - 09:56:38 CDT