MySQL SUBSTRING_INDEX() ফাংশন দিয়ে নাম স্ট্রিং তিন অংশে বিভক্ত করা

নিচের 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     |
+----------------------+----------------------+----------+---------------------+

নামকে First_Name, Middle_Name, Last_Name — তিন ভাগে ভাগ করতে:

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  |
+----------------------+------------+-------------+-----------+----------+

ফলাফলে দেখা যায় মিডল نیم না থাকলে NULL দেখায়।