Break Concatenated Field into Rows

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 2008 Forums

Transact-SQL (2008)

Break Concatenated Field into Rows

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2011-06-16 : 12:43:38

I have data stored in a table like below:

Patient Specimen Part(s); CPT Code(s)
SMELTZER, MICHAEL TED 88173; 88305CB
ELLER, MARION JOYCE 88173; 88172; 88172; 88305CB

Is there a way to pull it like this:

Patient Specimen Part(s); CPT Code(s)
SMELTZER, MICHAEL TED 88173
SMELTZER, MICHAEL TED 88305CB
ELLER, MARION JOYCE 88173
ELLER, MARION JOYCE 88172
ELLER, MARION JOYCE 88172
ELLER, MARION JOYCE 88305CB

Brian

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-16 : 13:04:21

Search this site for the ParseValues function
DECLARE @table table (string varchar(40))
INSERT INTO @table select 'SMELTZER, MICHAEL TED 88173; 88305CB'
select left(string,patindex('%[0-9]%',string)-1),a.val
from
@table
cross apply ( select val from dbo.ParseValues(substring(string,patindex('%[0-9]%',string),charindex(';',string)),';'))a

Jim

Everyday I learn something that somebody else already knew

bconner
Starting Member

48 Posts

Posted - 2011-06-16 : 14:50:51

Thanks Jim

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources