split data into seperate records by a semicolon

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

split data into seperate records by a semicolon

Author

Topic

Stephen Kligge
Starting Member

4 Posts

Posted - 2009-02-11 : 15:09:56

I have 2 tables (a header table and a detail table.) The detail table has an indentifier to the header table (DocID) but some of the columns have multiple records of data in one field seperated by a semicolon. I need to seperate the values out into seperate records but keeping the header data to each record. I am trying to do it as a query now, but eventually want to store the information in a seperate database. Any help would be appreciated as I need to get this project done as soon as possible.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-11 : 15:50:10

Read the topics in the section: CSV / Splitting delimeted lists

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

Be One with the Optimizer
TG

Stephen Kligge
Starting Member

4 Posts

Posted - 2009-02-11 : 16:36:51

I did not see anything in the list to anwser my questions. Below is what the 2 tbales look like currently

DataTable
56793621673210 2008-11-02 00:00:00.000 KELI MARTIN 1030 9465 4567;438;94 01;01;39 5793;4898;3482 6321;3848;4398 15
36298435673261 2008-04-19 00:00:00.000 BUCKWHEAT OPAL 4300 6777 3476;349;4034 02;93;28 4621;3482;2849 1834;2843;3428 16
12345678901234 2007-03-04 00:00:00.000 CHRIS MOORE 1245 2384 5679;2949;5493 10;48;28 1365;2849;3050 1234;2943;2498 17

DocIDTable
12 C:\Documents and Settings\cmoore\Desktop\acme\newimages\0000000C.TIF
15 C:\Documents and Settings\cmoore\Desktop\acme\newimages\0000000F.TIF
16 C:\Documents and Settings\cmoore\Desktop\acme\newimages\00000010.TIF
17 C:\Documents and Settings\cmoore\Desktop\acme\newimages\00000011.TIF

What I am trying to do is on the Data tbale break the columns that have multiple pieces of data seperated by a semicolon and make individual records for each referencing the header table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 16:44:23

SELECT dt.*, f.data
FROM DataTable AS dt
CROSS APPLY dbo.fnParseList(';', Col7) AS f

Function fnParseList is found in the link given earlier by TG.

E 12°55'05.63"
N 56°04'39.26"

Stephen Kligge
Starting Member

4 Posts

Posted - 2009-02-11 : 18:47:43

I found the FnParseList, but I have multiple columns with semicolons, is there a way to join all of them together as individual rows?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 23:53:28

try this too
declare @str1 varchar(max)
set @str1= 'M001111;M001222;M001333'

SELECT
replace(SUBSTRING(@str1,charindex(';',@str1,v.number),abs(charindex(';',@str1,charindex(';',@str1,v.number)+1)-charindex(';',@str1,v.number))),';','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(';' + @str1, v.number, 1) = ';'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 00:42:37

quote:
Originally posted by Stephen Kligge

I found the FnParseList, but I have multiple columns with semicolons, is there a way to join all of them together as individual rows?

Yes. Use UNION ALL for every column passed to CROSS APPLY.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources