RE: HOW TO SUBSTR & INSTR THIS LIST

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Wed, 27 Jun 2001 11:19:32 -0700
Message-ID: <F001.0033A717.20010627102606@fatcity.com>

Just as a starter you could start with the below

the first two decodes check whether there are any spaces and the third decode checks that the first and last space are different i.e there is a middle name.

The instr(full_name,' ',-1) is checking for a space from the end of the string.

You might have to look at rtrim and ltrim to dump any trailing/leading spaces and the code below will not deal with the Sophia Cadi-Soussi ( Gailhardou ) example.

Iain Nicoll

select decode(instr(full_name,' '), 0,full_name,

substr(full_name,1,instr(full_name,' ')-1)) first_name,

       decode(instr(full_name,' ',-1),0,null,     
        
substr(full_name,instr(full_name,' ',-1)+1)) last_name,          
       decode(instr(full_name,' ') - instr(full_name,' ',-1), 0, null,
 
substr(full_name, instr(full_name,' ')+1,       (instr(full_name,' ',-1)-1)
- (instr(full_name,' ')))) middle_name
from nametable

-----Original Message-----
Sent: 27 June 2001 17:27
To: Multiple recipients of list ORACLE-L

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: Nicoll, Iain (Calanais)
INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------