Formatting name column to 4 columns with comma sep

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/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources