How can we split the name string into three parts by 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.

Sharon Christine

Updated on: 20-Jun-2020

190 Views

Kickstart Your Career

Get certified by completing the course

Get Started