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.

Fendadis John

Published on 20-Feb-2018 13:05:52

• Related Questions & Answers
• How can we split the name string into three parts by using MySQL SUBSTRING_INDEX() function?
• How can we split an IP Address into four respective octets by using MySQL SUBSTRING_INDEX() function?
• How to split string in MySQL using SUBSTRING_INDEX?
• Split string into equal parts JavaScript
• 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 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?
• 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?
• What is the use of MySQL SUBSTRING_INDEX() function?
• How can we check that by default MySQL CHAR() function returns a binary string?