#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13

    Extracting comma delimited data to individual columns


    Hi

    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.
    J. Birdsell,
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    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.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13
    Originally Posted by kiteless
    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.
    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.

    thanks again for the input.
    J. Birdsell,
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13
    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:

    "15,23,40,55,44, p14,L1"

    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.

    Code:
    <!--- get the numbers --->
    <cfloop query='q3'>
    
    <cfset numList= #q3.numbers# >
    <!--- display the numbers in a table, strip off alpha characters --->
    
    <tr>
    				<cfoutput>
    					
    					<td>
    					#removeChars(listGetAt(numList, 1),1,1)#
    					</td>
    					<td>
    						#listGetAt(numList, 2)#
    					</td>
    					<td>
    						#listGetAt(numList, 3)#
    					</td>
    					<td>
    						#listGetAt(numList, 4)#
    					</td>
    					<td>
    						#listGetAt(numList, 5)#
    					</td>
    					<td >
    <!--- strip off leading alpha char --->
    			            #removeChars(listGetAt(numList,6),1,1)#
    					</td>
    					
    				</cfoutput>
    			</tr>
    		</cfloop>
    sample output:

    12/19/09 17 24 39 41 47 21

    Hope someone finds this useful.
    J. Birdsell,
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Originally Posted by jbird4k
    using listGetAt()
    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.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13
    Originally Posted by cfSearching
    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.
    thanks, I will keep that in mind. Fortunately this project the number of list elements are the same.
    J. Birdsell,

IMN logo majestic logo threadwatch logo seochat tools logo