From JTESTA@longaberger.com Wed, 27 Jun 2001 10:18:07 -0700 From: "JOE TESTA" Date: Wed, 27 Jun 2001 10:18:07 -0700 Subject: Re: HOW TO SUBSTR & INSTR THIS LIST Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 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 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@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> http://personal.mail.yahoo.com/> -- > Please see the official ORACLE-L FAQ:> http://www.orafaq.com> -- > Author: Stas>   INET: stant_98@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@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! Mailhttp://personal.mail.yahoo.com/