問題描述

如何在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

這篇關(guān)于如何在mysql中拆分名稱字符串?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！