Comma Delimited List 1

[stroll]

Hi,

I have a text field which has keywords listed for each study. These keywords are separated by a comma
for ex,

book,bed,pencil

I want to create a query which will take all of the keywords and create a seperate table which will allow the keys to be accessed by a key id.

Is there a way in sql to take out the words of a comma delimted list?

Thanks
Ryan

 

[kathryn]

Sorry, but there really isn't any easy way to do what you want, unless each record has exactly the same number of keywords.

Your table is not in "normal form". It would be worth your time in the long run to fix it.

if your current table looks like this:


ID StudyName Keywords
-- -------- --------
1 abc apple, banana, pear
2 bcd pear, banana
3 cde pineapple, apple

You need to change it to three tables (assuming that a keyword can be associated with more than one study):

tblStudy would look like this

StudyID StudyName
-- --------
1 abc
2 bcd
3 cde

tblKeyword

KeywordID Keyword
--------- -------
1 apple
2 banana
3 pear
4 pineapple


tblStudyKeyword

StudyKeywordID StudyID KeywordID
-------------- ------- ---------
1 1 1
2 1 2
3 1 3
4 2 3
5 2 2
6 3 4
7 3 1

If each keyword is only associated with one study, then you only need two tables.

I'm sure that this is not what you wanted to hear, but if you are going to be trying to get data out of a table, you are going to have continual problems with your current design.

Hope this helps.


Kathryn

 

[stroll]

Thanks. That is what we ended up doing.

Ryan