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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: HOW TO SUBSTR & INSTR THIS LIST

Re: HOW TO SUBSTR & INSTR THIS LIST

From: JOE TESTA <JTESTA_at_longaberger.com>
Date: Wed, 27 Jun 2001 10:18:07 -0700
Message-ID: <F001.0033A5C7.20010627095027@fatcity.com>

ok so i'm in a good mood, how about this:  
i input ur names into a table and ran this against it,  if u want to know what its doing email me if you can't figure it out.
 
joe

 
 
 

select substr(name,1,(instr(name,' ')-1)) first,       substr(name,instr(name,'
',-1,1)+1) last,       decode(instr(name,' ',1,2),0,NULL,        
substr(name,(instr(name,'
',1)+1),         (instr(name,'
',1,2) - (instr(name,' ',1,1)  )  )  ))from names;
>>> Stas <stant_98_at_yahoo.com> 06/27/01 12:11PM >>>That's the trick.I can figure out it with the first name ut what aboutthe rest.thanks!!!--- JOE TESTA <JTESTA_at_longaberger.com> wrote:> well if it was just first last, it would be simple> > select
substr(name_column,1,(instr(name_column,'> ')-1) first> > there is the part to pull off the first, i'll let> you experiment with the rest :)> > no reason to give you the WHOLE answer :)>
> joe> > > > > >>>

stant_98_at_yahoo.com 06/27/01 12:26PM >>>> Hey all,>
> I've got this list of names.> It's not a very structured
list.> So my question would be how do I get this names in a> select statement and break them up in columns: first> name (is the first name in list), last name (last> one),> middle name (everything in between first and last> names)> > I know that this may be done by using SUBSTR AND> INSTR.> > But how?>
> Would you please help?> > Thanks a lot.> >
Here is a fragment of the list of names:> > FULL_NAME               
> -------------------------> Caroline

Bernard         > Sophia
Cadi-Soussi ( Gailhardou )> Rudy
Sicard             
> Luis Haro-Garcma        
> Philip

Cohen            
> Socrates Fragoulis       > Michael

Munch            >
Hardip
Kaur             
> Robert

Szasz            
> Sebastien Schneider      > Telma
Quiroga Lspez      > Stiphanie 
Frenkel        > Samuel 
Tietse            

> Nicola

Rose             
> Oliver

Cornely           >
Philippe Saiag          
> M.t. Hamed Mosavian      > R. Bruce
Nicklas         > Valery 
Tsukerman         > > 
FULL_NAME                

> -------------------------> Lidiya
Smirenina         > Marie-Theres

Hauser      > Jelel
Ezzine            
> Radhi

Mhiri             
> Franco

Fenzi            
> Hachne Djellout         
> Beatrmz Quarterolo       > Bram

van Dam            
> Ted

Gaten               
> Sergio

Aravena           >
Alberto Monroy-Garcia    > Pedro Montecinos Becerra
> Michalis Vafopoulos      > Klaus E.

Gempel          > Guijun
Yan              
> Stiphane Schaak         
> > >
__________________________________________________> Do You 
Yahoo!?> Get personalized email addresses from Yahoo! Mail> <A href="http://personal.mail.yahoo.com/">http://personal.mail.yahoo.com/> -- > Please see the official ORACLE-L FAQ:> <A href="http://www.orafaq.com">http://www.orafaq.com> -- > Author: Stas>   INET: stant_98_at_yahoo.com> > Fat City Network Services    -- (858) 538-5051  FAX:> (858) 538-5051> San Diego,
California        -- Public Internet>
access / Mailing
Lists>--------------------------------------------------------------------> 
To REMOVE yourself from this mailing list, send an> E-Mail message> to: ListGuru_at_fatcity.com (note EXACT spelling of> 'ListGuru') and in> the message BODY, include a line containing: UNSUB> ORACLE-L> (or the name of mailing list you want to be removed> from).  You may> also send the HELP command for other information> (like subscribing).>
__________________________________________________Do You 
Yahoo!?Get personalized email addresses from Yahoo! Mail<A href="http://personal.mail.yahoo.com/">http://personal.mail.yahoo.com/ Received on Wed Jun 27 2001 - 12:18:07 CDT

Original text of this message

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