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 2000 Forums
 SQL Server Development (2000)
 Splitting comma-separated text in a field

Author  Topic 

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-20 : 10:52:47
I have a table of Publications with a very typical structure (ISBN, Title, Authors,…etc). I want to put the Authors data in a separate table (ISBN,Authors) –actually it’s a bit more complicated than that but I’m keeping it simple for MY benefit. The problem I have is that some of the publications have up to 20 authors in the field separated by commas. Does anyone have a neat little stored procedure or function that can do this for me. I’m almost embarrassed to say it, but up until now I’ve been exporting this to MS Excel as a CSV file and then doing convoluted manipulations before re-importing it into a database. I give myself 0/10 for elegance!
Cheers,
Sam

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 11:01:17
You can use a splitter function

INSERT INTO MyTable(Author)
SELECT Value
FROM dbo.splitter(@ListOfAuthors)

There are a number of splitter functions listed here (try searching for CSV and SPLITTER) or at
http://www.sommarskog.se/arrays-in-sql.html

Kristen
Go to Top of Page

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-20 : 15:07:07
As ever Kristen, you've saved me from wasting a few hours of my life!
Many thanks,
Sam
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 16:46:11
No problem.

My advice would be to take the trouble and make a NUMBERS table - its loads faster

Kristen
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-20 : 17:35:30
Here's a nice approach from our very own Rob Volk: http://www.sqlteam.com/item.asp?ItemID=2652

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering
Go to Top of Page

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-21 : 05:53:45
Thanks for the link. My site search didn't pull this.
Cheers,
Sam
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-21 : 20:14:02
quote:
Originally posted by samtoffa

My site search didn't pull this.

That may have been because there are two different searches on this site. One for articles and another for forum posts. I'm sure putting these together somehow is on Graz's wish list for the next version.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering
Go to Top of Page
   

- Advertisement -