如何在mysql中拆分名称字符串?

2021-11-20 00:00:00 split sql mysql substring

如何在mysql中拆分名称字符串?

How to split the name string in mysql ?

例如:

name
-----
Sachin ramesh tendulkar
Rahul dravid

将名称拆分为firstname,middlename,lastname:

firstname   middlename    lastname
---------  ------------   ------------
sachin     ramesh         tendulkar
rahul      dravid

推荐答案

我已将此答案分为两 (2) 个方法.第一种方法将您的全名字段分为名字、中间名和姓氏.如果没有中间名，中间名将显示为NULL.

I've separated this answer into two(2) methods. The first method will separate your fullname field into first, middle, and last names. The middle name will show as NULL if there is no middle name.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
   If(  length(fullname) - length(replace(fullname, ' ', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 2), ' ', -1) ,NULL) 
           as middle_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 3), ' ', -1) AS last_name
FROM registeredusers

第二种方法将中间名视为姓氏的一部分.我们只会从您的全名字段中选择名字和姓氏列.

This second method considers the middle name as part of the lastname. We will only select a firstname and lastname column from your fullname field.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
    TRIM( SUBSTR(fullname, LOCATE(' ', fullname)) ) AS last_name
FROM registeredusers

您可以使用 substr、locate、substring_index 等来做很多很酷的事情.检查手册是否有真正的困惑.http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

There's a bunch of cool things you can do with substr, locate, substring_index, etc. Check the manual for some real confusion. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

相关文章