From JTESTA@longaberger.com Wed, 27 Jun 2001 11:10:21 -0700 From: "JOE TESTA" Date: Wed, 27 Jun 2001 11:10:21 -0700 Subject: Re: HOW TO SUBSTR & INSTR THIS LIST Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 aselect statement and break them up in columns: firstname (is the first name in list), last name (lastone),middle name (everything in between first and lastnames)I know that this may be done by using SUBSTR ANDINSTR.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! Mailhttp://personal.mail.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Stas  INET: stant_98@yahoo.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).