Best sql data type to store comma delimited lists
Posted on 2006-11-11
What's the best data type and data length to store large comma delimited lists? I'm using SQL 2000 and CF5. The list is stored into a column named POP_KEYWORDS. It's always accessed as one list, not individual elements.
I may be using cfquery to transfer the entire list from POP_KEYWORDS to another database table where the new list in the new table will then be broken down into individual elements. POP_KEYWORDS is accessed rarely and only by myself to manually transfer that list elsewhere. It's just a storage of popular keywords.
Each POP_KEYWORDS list can hold around 6000 characters, including commas. But this may be longer in the future. Should I use a VARCHAR data type or can I use TEXT as a data type and will it accept comma delimited text? Remember, this is just storage. The list will not be modified from the original storage column.
And if I decide to simply save POP_KEYWORDS into a text file on the hard drive, what format should that file be, CSV? And how does one access a text file and copy it into ar database, like this?
<cfset newpoplist = "c:\lists\keywords\list1.csv">
Is that the correct way to get it into a list ready for a query?