逗号前后的字符串交换

Question

我有下面的桌子

AdviserName
-----------
Tearle, Michael Hearn
Hearn Tearle, Michael
Van Der Welden, Luke
Welden, Luke Van Der
Brennan, Sony G
G Brennan, Sony
Rhammasamysundaran, Vishnu Brahma Kuma 
Brahma Kuma Rhammasamysundaran, Vishnu 

基本上，我需要重新安排，这样它就变成了FirstName MiddleName LastName。例如，迈克尔·赫恩·泰尔()、索尼G布伦南()或卢克·范德韦尔登()。

我用了下面的语法，但不知何故它不喜欢中间的名字在逗号前面，比如Tearle，Michael。

If(OBJECT_ID('tempdb..#tempX') Is Not Null)

Drop Table #tempX

create table #tempX (AdviserName varchar(max))

insert into #tempX (AdviserName)
values ('Tearle, Michael Hearn'),('Hearn Tearle, Michael'),('Van Der Welden, Luke'),('Welden, Luke Van Der'),('Brennan, Sony G'),('G Brennan, Sony'),('Rhammasamysundaran, Vishnu Brahma Kuma '), ('Brahma Kuma Rhammasamysundaran, Vishnu ')

--select * from #tempX


select substring(advisername, charindex(',', replace(advisername, ' ', '')) + 1, len(advisername))+' '+ left(advisername, charindex(',', advisername) -1) as AdviserName from #tempX

下面是结果。

AdviserName
--------------------------------------
 Michael Hearn Tearle
, Michael Hearn Tearle
n, Luke Van Der Welden
 Luke Van Der Welden
 Sony G Brennan
, Sony G Brennan
 Vishnu Brahma Kuma  Rhammasamysundaran
n, Vishnu  Brahma Kuma Rhammasamysundaran

如何在逗号之前和逗号之后进行查询以满足中间名的要求？

我正在使用Server 2012。

谢谢大家。

Answer 1

尝尝这个

If(OBJECT_ID('tempdb..#tempX') Is Not Null)

Drop Table #tempX

create table #tempX (AdviserName varchar(max))

insert into #tempX (AdviserName)
values ('Tearle, Michael Hearn'),('Hearn Tearle, Michael'),('Van Der Welden, Luke'),('Welden, Luke Van Der'),('Brennan, Sony G'),('G Brennan, Sony'),('Rhammasamysundaran, Vishnu Brahma Kuma '), ('Brahma Kuma Rhammasamysundaran, Vishnu ')


select
    AdviserName,
    replace(replace(
    SUBSTRING(AdviserName,charindex(',',AdviserName),len(AdviserName))
    +' '+
     SUBSTRING(AdviserName,1,charindex(',',AdviserName))    
     ,',',''),'  ',' ')
from #tempX

Answer 2

;with d as
(select AdviserName, charindex(',',AdviserName) as comma from #tempX)
select
ltrim(rtrim(substring(advisername,comma+1,100)))+' '+ltrim(rtrim(left(advisername,comma-1)))
from d