Removing null from a value list

Question

I have a query :

<cfif topic NEQ "">
    <cfquery name="queryTopicName" datasource="#ODBC#">
        select topic as topicName from ltbTopics where topicId in (#topic#)
    </cfquery>
    <cfset selectedRiskCategories = ValueList(queryTopicName.topicName)>
</cfif>

Here the "#topic#" contains a list whose first value is empty, so it comes likes ,,51,52, etc so it gives an error as:

"Incorrect syntax near ','

.
The error occurred on line 33", can any one help me in this how to resolve this?

Answer 1

There are many ways to do this.But a simple hack is convert the list to an array and then back to list.

<cfif topic NEQ "">
 <cfset arrayTopic = ListToArray(topic)>
 <cfset topic = ArrayToList(arrayTopic)>
 <!---you may need some more validations as it is possible that original list only has commas in it--->
   <cfquery name="queryTopicName" datasource="#ODBC#">
      select topic as topicName from ltbTopics where topicId in (#topic#)
   </cfquery>
   <cfset selectedRiskCategories = ValueList(queryTopicName.topicName)>
</cfif>