Re: HOW TO SUBSTR & INSTR THIS LIST

[JOE TESTA]

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 <[EMAIL PROTECTED]> 06/27/01 12:11PM >>> That's the trick. I can figure out it with the first name ut what about the rest. thanks!!! --- JOE TESTA <[EMAIL PROTECTED]> 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 > > > > > >>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 http://personal.mail.yahoo.com/