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 11:49:23

• Related Questions & Answers
• How can we split the name string into two parts by using MySQL SUBSTRING_INDEX() function?
• Split string into equal parts JavaScript
• How can we split an IP Address into four respective octets by using MySQL SUBSTRING_INDEX() function?
• How can we upload data into MySQL tables by using mysqlimport?
• How can we upload data into multiple MySQL tables by using mysqlimport?
• How can we import CSV files into MySQL tables by using mysqlimport?
• How we can split Python class into multiple files?
• How can we extract a substring from a string in MySQL?
• How can we divide the result set returned by MySQL into groups?
• How can we split a string by sentence as a delimiter in Java?
• How can we reverse a MySQL string connected by the dash?
• Program to check a string can be split into three palindromes or not in Python
• How can we set up a MySQL User account by using INSERT INTO statement?
• How can we insert data into an existing MySQL table by using PHP script?
• How can we replace all the occurrences of a substring with another substring within a string in MySQL?