Re: instr question

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