Spliting a string of codes separated by commas

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

Transact-SQL (2000)

Spliting a string of codes separated by commas

Author

Topic

ajaeger
Starting Member

4 Posts

Posted - 2003-02-12 : 17:06:09

I have a table that I will need to reformat periodically. The data will come to me in the following format (field names are ID and KEYWORDS, I think the display will look a little funny. I used the hyphens to space out the 2 columns - they don't seem to format well. Hope it's not too confusing.)

ID - KEYWORDS
10 - KEYWORD1,KEYWORD2
11 - KEYWORD3,KEYWORD4,KEYWORD5,KEYWORD6
12 - KEYWORD7,KEYWORD8,KEYWORD9

There will be one row per ID in the table and it can have any number of different KEYWORDS. KEYWORDS will always be delimited by a comma and will always be 8 characters long.

I need the data in another table the following format. (Field names are SEQN, a new field not in the above table, ID from the above table and KEYWORD. Again, I used the hyphens to space out the 3 different columns.)

SEQN - ID - KEYWORD
1 - 10 - KEYWORD1
2 - 10 - KEYWORD2
3 - 11 - KEYWORD3
4 - 11 - KEYWORD4
5 - 11 - KEYWORD5
6 - 11 - KEYWORD6
7 - 12 - KEYWORD7
8 - 12 - KEYWORD8
9 - 12 - KEYWORD9

Any suggestions? Thanks a bunch!