How to split the name string in mysql?

Question

ow to split the name string in mysql ?

E.g.:

name
-----
Sachin ramesh tendulkar
Rahul dravid

Split the name like firstname,middlename,lastname:

firstname   middlename    lastname
---------  ------------   ------------
sachin     ramesh         tendulkar
rahul      dravid

Answer 1

To split the name string in MySQL

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name, If( length(fullname) - length(replace(fullname, ' ', ''))>1, SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 2), ' ', -1) ,NULL) as middle_name, SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 3), ' ', -1) AS last_name FROM registeredusers