Swap Comma Delimated Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Swap Comma Delimated Value

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-03-21 : 01:55:24

I have the sample value with comma delimated value in Column [Name], Which contain in the format of LastName, FirstName and in bracket some ID. i want to swap FirstName and Last Name without Number. Some cases there is no comma, On that case the actual data need to be displayed.

For Example

Name
------
ABCD, QWER T (3221)
XYZZ, FGHJ CS (6788)
ACSD, TYUW (1212)
POU RTYE (232)

Expected output is

Name
-------

QWER ABCD
FGHJ XYZZ
TYUW ACSD
POU RTYE

Please
Let me know how to do this in SQL Statment

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-21 : 06:29:18

declare @swapname varchar(50)

declare @stringtable table
( fullname varchar(20) )
insert into @stringtable select 'Friel, Joe (3221)'

select @swapname = LEFT(fullname,CHARINDEX ('(',fullname)-1) from @stringtable

select RIGHT(@swapname,( len(@swapname ) - charindex(',',@swapname)) )+ '' + left (@swapname,( charindex(',',@swapname) -1 ))
from @stringtable

--was this helpful

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:10:43

first thing to consider is whether the format is consistent across ie will there be , separator and number at end always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-22 : 01:59:30

If the format is consistant, try this too

select parsename(name,1)+parsename(name,2) from
(
select replace(substring(name,1,charindex('(',name)-1),',','.') as name from table
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources