Thread: How To get First,middle and last name from a column?

Hi friends, i have one problam,please give me the solution. I have column in a with name.now i want to select that names as first name,second name,third name. I/p---- names chandra sekhar balaji. Giri koti reddy nagendra lakshman bala now i want o/p like. Firstname secondname thirdname chandra sekhar balaji giri koti reddy nagendra lakshman bala

Here is the query

SQL> SELECT substr(name,1,instr(name,' ',1,1)-1) Firstname,
substr(name,instr(name,' ',1,1)+1,(instr(name,' ',1,2)-instr(name,' ',1,1))-1) Middlename,
substr(name,instr(name,' ',1,2)+1) Lastname
FROM test;

FIRSTNAME MIDDLENAME LASTNAME
------------------------------ ------------------------------ ------------------------------
chandra sekhar balaji
Giri koti reddy
nagendra lakshman bala

SQL>

Select substr('giri koti reddy nagendra lakshman bala',1, instr('giri koti reddy nagendra lakshman bala',' ',1)) first_name, substr('giri koti reddy nagendra lakshman bala',instr('giri koti reddy nagendra lakshman bala',' ',1)+1, instr('giri koti reddy nagendra lakshman bala',' ',2)) middle_name, substr('giri koti reddy nagendra lakshman bala',instr('giri koti reddy nagendra lakshman bala',' ',-2)+1, instr('giri koti reddy nagendra lakshman bala',' ',-1)) last_name from dual