ListToQuery UDF

Today I happened to find myself at CFlib.org - I don’t go there often - and I was browsing the newer function submissions just for the heck of it and I came across the function ListToQuery by Russ Spivey. It’s a straight forward enough function but I thought it could be a little cleaner, and potentially a little faster when dealing with large lists.

Here is my version:

<cffunction name="listToQuery" access="public" returntype="query"
output="false"
hint="Converts a list to a single-column query.">

<cfargument name="list" type="string" required="yes" hint="List to
convert.">
<cfargument name="delimiters" type="string" required="no" default=","
hint="Things that separate list elements.">
<cfargument name="columnName" type="string" required="no" default="column"
hint="Name to give query column.">
<cfargument name="includeBlanks" type="boolean" required="no"
default="false" hint="include empty elements in the list as empty values in
the query">

<cfset var query = queryNew("")>
<cfset QueryAddColumn(query,arguments. columnName,ListToArray(arguments.lis
t,arguments.delimiters,arguments.includeBlanks))>

<cfreturn query>

</cffunction>

Sadly, I couldn’t contact Russ directly to share my contribution so hopefully he doesn’t mind me sticking this here. If it weren’t for his initial cut at the function I wouldn’t have thought to even try and write it.

If anyone wants to benchmark the two it would probably be interesting to see if my intuition is correct. UPDATE: Ok, well, I benchmarked it with 5000, 10000 and 30000 element lists (I didn’t have the patience for a 100,000 element list sorry (i suppose I could optimize my list building). Here were the results:

5000 element list Function| Time in ms —|—

10000 element list Function| Time in ms —|—

30000 element list Function| Time in ms —|—