如何使用 MySQL SUBSTRING_INDEX() 函数将名称字符串拆分为三部分?
mysqlmysqli database
为了便于理解，我们使用名为"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)
现在，假设我们想将名字分成三个部分，‘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 | +----------------------+------------+-------------+-----------+----------+ 4 rows in set (0.00 sec)
从上述查询的结果集可以看出，姓名被分成了三个部分。如果姓名不包含中间名，则将中间名视为 NULL。
