How can we split the name string into two 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 | Rajasthan | 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 two parts, ‘First_name’ and ‘Last_name’ then it can be done with the help of the following query −
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1) AS First_Name, TRIM( SUBSTR(Name, LOCATE(' ', Name)) ) AS Last_Name FROM Customerdetail;
+------------+--------------+
| First_Name | Last_Name |
+------------+--------------+
| Advik | Jhamb |
| Chirag | Jai Patil |
| Devansh | Singh Rajput |
| Mitul | Kumar Sharma |
+------------+--------------+
4 rows in set (0.00 sec)From the result set of above query, it is clear that the name has been divided into two parts. It considers the middle name as a part of the last name.
Published on 20-Feb-2018 13:05:52
