Re: instr question

Thank you Brian,

When I sort of plug in my current field and current table just to see what results I get, I get the following error

SELECT SUBSTR(so_act_arg,INSTR(so_act_arg),' ')+1, INSTR(so_act_arg,' ',1,2) - NSTR(so_act_arg),' ')-1) FROM appworx.so_object_cond
WHERE so_act_arg LIKE '-m%';

FROM keyword not found where expected. What am I doing wrong?

Brian Peasland wrote:
> kimberly.shaffer_at_gmail.com wrote:
> > I am using oracle 9.2 and am a newbie trying to figure out the instr
> > argument, and just can't wrap my brain around it. Or maybe I'm going
> > about this the wrong way with the wrong function. Say I have a simple
> > statement:
> >
> > select currentfield from current table;
> >
> > and it brings me back something like
> >
> > -m first_string next_string one_more_string last_string
> > -m second_string next_string one_more_string last_string
> > -m third_string next_string one_more_string last_string
> >
> > and I just want it to look like (in my return)
> > first_string
> > second_string
> > third_string
> >
> > and strip out all the remaining strings and the -m prefix
> >
> > is that possible? Can someone example me with this so I can see where
> > I am going wrong?
> >
> > thx in advance and apologies if this is really simple.
> >
> > ks
> >
>
> So if I get what you are trying to do, you want to remove all but the
> characters between the first and second space. The INSTR function can be
> used to find the space.
>
> INSTR(currentfield,' ') returns the position of the first space.
> INSTR(currentfield,' ',1,2) returns the position of the second space
> (starting at the 1st character in the column).
>
> Now that you have that, use the SUBSTR function to return the characters
> between those spaces:
>
> SELECT SUBSTR(currentfield,INSTR(currentfield),' ')+1,
> INSTR(currentfield,' ',1,2) - NSTR(currentfield),' ')-1)
> FROM current_table;
>
>
> 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
Received on Thu Jul 20 2006 - 13:55:56 CDT