This question already has answers here:
How to split a single column values to multiple column values?

(7个答案)


4年前关闭。





我有一个表tblPerson和列fullname

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


我想爆炸全名列,以获取姓氏,名字和中间名,并分别创建3列。

我正在使用MS SQL Server 2008 R2

输出:

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


先感谢您

最佳答案

最简单的解决方案是

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


要么

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

08-26 18:33
查看更多