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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Formatting name column to 4 columns with comma sep

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-01-19 : 08:19:51
I have the following professional names in a table under one column Name_full_formatted, need to split as 4 with comma separated
1st part is last name
3rd part is first name
4th part is middle initial
2nd part id title


should be 1,3,4,2
some rows there is no middle initial just want to still show a comma.



Declare @Sample table (Name_Full_Formatted varchar(100))
insert @Sample
select 'Vargas-Posada MD, Esperanza' union all
select 'Velaszuez MD , Fernando J' union all
select 'Verdecia Sr. MD , Luis F' ;

select * from @Sample


Thank you very much for the helpful info.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-19 : 08:54:42
See this article:

http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-21 : 03:51:53
select left(Name_Full_Formatted, charindex(',', Name_Full_Formatted)-1),
substring(Name_Full_Formatted, nullif(charindex(',', Name_Full_Formatted, charindex(',', Name_Full_Formatted)+1),0), LEN(Name_Full_Formatted))
from @Sample

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -