mysql 拆分字符串为单个字符,如何在mysql中拆分名称字符串？

How to split the name string in mysql ?

E.g.:

name

-----

Sachin ramesh tendulkar

Rahul dravid

Split the name like firstname,middlename,lastname:

firstname middlename lastname

--------- ------------ ------------

sachin ramesh tendulkar

rahul dravid

解决方案

I've seperated 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

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