If I have a table with a column that contains fullnames such as :
select * from name table;
name table
| ID | NAME |
| 101 | sachin ramesh tendulkar |
| 102 | virat koholi |
| 103 | rabi sastri |
| 104 | thakur ramesh kar |
| 105 | pratap ram sek |
I retrieve the first, middle and last name from each of the entries in the full name column using SQL
output
| ID | NAME | FIRST_NAME | MIDDLE_NAME | LAST_NAME |
| 101 | sachin ramesh tendulkar | sachin | ramesh | tendulkar |
| 102 | virat koholi | virat | NULL | koholi |
| 103 | rabi sastri | rabi | NULL | sastri |
| 104 | thakur ramesh kar | thakur | ramesh | kar |
| 105 | pratap ram sek | pratap | ram | sek |
The Query
select id,name,
substr(name,1,instr(name,' ',1,1)-1) as first_name,
substr(name,instr(name,' ',1,1)+1,(instr(name,' ',1,2)- instr(name,' ',1,1)-1) ) as middle_name,
substr(name,instr(name,' ',-1)+1) as last_name
from t_name1; 