# Databases > Oracle How To get First,middle and last name from a column?

## chandra.pc

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

----------


## krishnaindia2007

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>

----------


## satishorre

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

----------

