Re: HOW TO SUBSTR & INSTR THIS LIST

Hi

I asume a space is the delimitor in Your column. First cut from beginning to position of first space minus 1 to get first_name,
the last name by cutting from position of first space found from aft plus one (to get rid of the space) to end of string, and the midle_name by substring from first space plus one; the substring length is position of first space from aft; minus first space found from front minus 1
IE:
select substr(full_name,1,instr(full_name,' ',1)-1) first_name,

       substr(full_name,instr(full_name,' ',1)+1,instr(full_name,' ',-1)-instr(full_name,' ',1)-1) midle_name,

       substr(full_name,instr(full_name,' ',-1)+1) last_name from table

You might have to substitute full_name with replace(full_name, '-'.) to get rid of dashes
or replace( repalce (full_name,'(' ) , '(' ) to get rid of parentesis.

Databases have it - junk in => shit out

Rgds
Svend Jensen

Stas wrote:

> 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_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Svend Jensen
  INET: svend.jensen_at_it.dk

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).