SQL:将列值分割为多个列[重复]

This question already has an answer here:

这个问题已经有了答案:

• How to split a single column values to multiple column values? 6 answers
• 如何将一个列值分割为多个列值?6答案

I have a table tblPerson and column fullname

我有一个表tblPerson和列全称

--------------
fullname   
--------------
Garcia, John C.
Herdan, Stephen S.
Ubico, Ed Z.

I want to explode the fullname column in order to get the last, first and middle name and create 3 columns respectively.

我要对fullname列进行拆分，以便获得最后的、第一个和中间的名称，并分别创建3列。

I am using MS SQL Server 2008 R2

我正在使用MS SQL Server 2008 R2

Output:

输出:

------------------------------------------------------------------------
   fullname              lname             fname               mname
------------------------------------------------------------------------
Garcia, John C.          Garcia,           John                C.
Herdan, Stephen S.       Herdan,           Stephen             S.
Ubico, Ed Z.             Ubico,            Ed                  Z.

Thank You in Advance

提前谢谢你

3 个解决方案

#1

Easiest solution would be,

简单的解决方案是,

SELECT fullname,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,1)) AS lname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,2)) AS fname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,3)) AS mname
FROM   tblPerson

OR

或

SELECT fullname,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,1)) AS lname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,2)) AS fname
      ,STUFF(fullname, 1, Len(fullname) +1- CHARINDEX(' ',Reverse(fullname)), '') mname
FROM   tblPerson

sql fiddle demo

sql小提琴演示

#2

This one will support multi-word last and first names:

这一个将支持多字的姓和名:

SELECT 
CASE WHEN fullname LIKE '%, %' THEN SUBSTRING(fullname, 1, CHARINDEX(', ', fullname)) ELSE fullname END AS lname,
CASE 
WHEN fullname LIKE '%, % %.' THEN SUBSTRING(fullname, CHARINDEX(', ', fullname)+2, LEN(fullname)-CHARINDEX(' ', REVERSE(fullname)) - CHARINDEX(', ', fullname)-1) 
WHEN fullname LIKE '%, %' THEN SUBSTRING(fullname, CHARINDEX(', ', fullname)+2, LEN(fullname)- CHARINDEX(', ', fullname)-1) 
END AS fname,
CASE WHEN fullname LIKE '%, % %.' THEN SUBSTRING(fullname, LEN(fullname)-CHARINDEX(' ', REVERSE(fullname)) + 2, 1000) END AS mname
FROM tblPerson

#3

Try;

试着;

;with data_tbl as (
    select [fullname],
    CHARINDEX(',', [fullname]) + 1 index_comma,
    CHARINDEX(' ', [fullname], CHARINDEX(',', [fullname]) + 2) index_space,
    len([fullname]) len_name
    from tblPerson
    --where [fullname] like '%, % %'
)
select 
    [fullname],
    left([fullname], index_comma - 1) lname,
    substring([fullname], index_comma, index_space - index_comma) fname,
    substring([fullname], index_space, len_name) mname
from data_tbl

sql fiddle demo

sql小提琴演示

If you add the where condition where [fullname] like '%, % %' then it will ignore all the fullname that is not in the fromat %, % %

如果您添加where条件，其中[fullname]如'%，%'，那么它将忽略所有不在fromat %中的fullname