CODEWITHSUNDEEP
sql-serverstringswap
Adhitya Sanusi
Adhitya Sanusi

Reputation: 119

String Swap Before and After Comma

I have the table below

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

Basically I need to re-arrange so it become FirstName MiddleName LastName. For example Michael Hearn Tearle or Sony G Brennan or Luke Van Der Welden.

I used below syntax but somehow it doesn't like if the middle name is before the comma like Hearn 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

Below is the result. 

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

How do you make a query to cater both middle name before comma and after comma?

I'm using SQL Server 2012.

Thanks all.

Upvotes: 0

Views: 305

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Try this

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

Upvotes: 1

LoztInSpace
LoztInSpace

Reputation: 5697

;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

Upvotes: 1

Related Questions