sql – how to split full name into first and middle and last name

sql – how to split full name into first and middle and last name

If I have a table with a column that contains fullnames such as :

select * from name table;

name table

IDNAME
101sachin ramesh tendulkar
102virat koholi
103rabi sastri
104thakur ramesh kar
105pratap ram sek

I retrieve the first, middle and last name from each of the entries in the full name column using SQL

output

IDNAMEFIRST_NAMEMIDDLE_NAMELAST_NAME
101sachin ramesh tendulkarsachinrameshtendulkar
102virat koholiviratNULLkoholi
103rabi sastrirabiNULLsastri
104thakur ramesh karthakurrameshkar
105pratap ram sekpratapramsek
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;

Leave a Reply