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.
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 functionINSERT INTO MyTable(Author)SELECT ValueFROM dbo.splitter(@ListOfAuthors)There are a number of splitter functions listed here (try searching for CSV and SPLITTER) or athttp://www.sommarskog.se/arrays-in-sql.htmlKristen |
|
|
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 |
|
|
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 fasterKristen |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
|
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 |
|
|
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 |
|
|
|
|
|