Split Name String into Three Parts Using MySQL SUBSTRING_INDEX Function

To make it understand, we are using the following data from a table named ‘customerdetail’.

mysql> Select * from Customerdetail;
+----------------------+----------------------+----------+---------------------+
| Name                 | FName                | Address  | Emailid             |
+----------------------+----------------------+----------+---------------------+
| Advik Jhamb          | Lovkesh Jhamb        | Mumbai   | Advik@gmail.com     |
| Chirag Jai Patil     | Raman Jai Patil      | Gujrat   | chirahp@yahoo.com   |
| Devansh Singh Rajput | Kishore Singh Rajput | Rajastan | Devansh@Hotmail.com |
| Mitul Kumar Sharma   | Om Veer Sharma       | Patiala  | Mitul@gmail.com     |
+----------------------+----------------------+----------+---------------------+
4 rows in set (0.00 sec)

Now, suppose if we want to split the name into three parts, ‘First_name’, Middle_Name and ‘Last_name’ then it can be done with the help of the following query −

mysql> SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1) AS First_Name, If( length(Name) - length(replace(Name, ' ', ''))>1, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1) ,NULL)AS Middle_Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 3), ' ', -1) AS Last_Name, Address FROM customerdetail;
+----------------------+------------+-------------+-----------+----------+
| Name                 | First_Name | Middle_Name | Last_Name | Address  |
+----------------------+------------+-------------+-----------+----------+
| Advik Jhamb          | Advik      | NULL        | Jhamb     | Mumbai   |
| Chirag Jai Patil     | Chirag     | Jai         | Patil     | Gujrat   |
| Devansh Singh Rajput | Devansh    | Singh       | Rajput    | Rajastan |
| Mitul Kumar Sharma   | Mitul      | Kumar       | Sharma    | Patiala  |
+----------------------+------------+-------------+-----------+----------+
4 rows in set (0.00 sec)

From the result set of above query, it is clear that the name has been divided into three parts. It considers the middle name as NULL if the name does not consist middle name.