December 23rd, 2012, 10:32 AM
Extracting comma delimited data to individual columns
I am working with a data base and one of the fields containts comma delimited data. 1,2,3,4,5,6,...
I have to display each value from this field in its own column in the out put. I am experiencing a major mental malfunction.
I tried treating the field as a list and looping over it but I keep getting errors such as: Complex object types cannot be converted to simple values.
can put each record into its own column, but how do i get each record to display one value in each column and one row for each record?
sorry if this seems basic to you all but at the moment i'm brain dead.
Thanks in advance for any infor or examples you can provide.
December 23rd, 2012, 01:32 PM
If you select this column in a query, you can treat it as a list and loop over it. But the correct thing to do is to break this out into a separate table with one row for each value, and use a join in your query. Basically, it's almost always a bad idea to store a list of values in one column.
December 23rd, 2012, 04:10 PM
thanks, Kiteless, Normally when I create the tables, I have a column for each form value, However,the table I was given wasn't set up that way.
Originally Posted by kiteless
thanks again for the input.
December 24th, 2012, 05:22 AM
Here is my solution, in case anyone finds themselves is a similar situation.
The data i had to work with was 2 fields a date field and a numbers field. the numbers field contained data like this:
the requirement was to display the data above in a table with each value in its own column. this was achived byusing listGetAt() to display the values.
below is the snippet of code used to meet the display requirement.
<!--- get the numbers --->
<cfset numList= #q3.numbers# >
<!--- display the numbers in a table, strip off alpha characters --->
<!--- strip off leading alpha char --->
12/19/09 17 24 39 41 47 21
Hope someone finds this useful.
December 24th, 2012, 12:59 PM
It's also worth mentioning that some database systems have features to query on and work with lists of values like this. So it could be worth spending a few minutes just poking around in the docs for your particular database platform to see if it supports anything like this.
December 24th, 2012, 02:47 PM
As an aside, I am not sure if all of your values have the same number of list elements. If not, you might look into using getToken(). It essentially does the same thing, but if you try and grab a the list position does not exist, getTokent() returns an empty string. Whereas listGetAt() throws an error.
Originally Posted by jbird4k
January 3rd, 2013, 08:58 PM
thanks, I will keep that in mind. Fortunately this project the number of list elements are the same.
Originally Posted by cfSearching