If you’ve ever had the need to convert a query column to an array, there are a couple ways to do it. There is a queryColumnToArray function on cflib, or you could roll your own, but you can also do it with the built in ValueList and ListToArray functions.

Given the query “myQuery”, and a column called “name”, you can turn that into an array with this:

<cfset myArray = ListToArray(ValueList(myQuery.name)) />

The only problem with this is if the name column were to ever contain a comma, that value will get split into separate values, probably causing problems later on in your code. I ran into this issue with some old code the other day. The quick solution was to use a different delimiter than the default, which is a comma. You can pick a bizarre ascii character that you are unlikely to encounter in your data. I usually use one of the unprintable characters like 31, a “unit separator”, which sounds like a good one, doesn’t it?

<cfset myArray = ListToArray(ValueList(myQuery.name, Chr(31) ), Chr(31) ) />

2 Comments

  1. Ghalstead says:

    Thank you for the nice info.

  2. David says:

    What would happen to black or null values in the query col?

    Would valuelist ignore them?

Leave a Reply

You must be logged in to post a comment.