ColdFusion List Question

[apocalipsis19]

Hello Community!

I have two questions regarding CF lists:

1-) I have a list that has the values 1,2,

As you can see, the comma at the end of that string will make mymy program break because that list of comma delimited values is being used in a SQL statement: in(1,2,)

How do I remove that comma at the end of my list?

2-) I have a list with the values 1,,3

How do I get rid of that unnecesary comma? How do I fill in the space between , and , with a value so it's 1,value,3 instead?

Thanks!

Ysais.

[-__cfSearching__-]

There are several options (like replace).  But, CF list functions ignore empty elements.  This is one situation where that behavior might be useful. One option is to convert the list to an array and then back to a list.  That will get rid of the extra commas

<cfset newList = arrayToList(listToArray(yourList))>

Just check the list length before passing it to your sql, to avoid an error if the list is empty.

[Guest]

if mylist = '1,2,'

then you could make your SQL in(#removechars(mylist, len(mylist), 1)#)

essentially removing the last character.

to get rid of the unnecessary comma you could CFLOOP though the list and create another list 'mynewlist' this time not including anything that's blank

ie if mylist = '1,,3'

<cfset mynewlist = ''>
<cfloop list="#mylist#" index="getitem">
<cfif getitem neq ''>
<cfset mynewlist = listappend(mynewlist, #getitem#)>
</cfif>
</cfloop>

mynewlist becomes '1,3'

[-__cfSearching__-]

you could CFLOOP though the list and create another list 'mynewlist' this time not including anything that's blank

If the goal is to remove extra commas, it is simpler to just use:  newList = arrayToList(listToArray(oldList)).

[insuractive]

Have you tried just using the original list in your SQL statement as a <cfqueryparam> tag with attribute list="yes" ?  Not sure if it will still through an error, but its definitely worth using the <cfqueryparam> tags to handle your lists in your SQL: faster and less vulnerable to attack.

[insuractive]

Just tried it and it looks like cfqueryparam list="yes" will clean up your list for you nicely:

<cfset sList = ",1,,2,3,">

<CFQUERY NAME="qTest" DataSource="#Application.SMA_DataSource#">
SELECT *

FROM SomeTable

WHERE ID in (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#sList#" list="yes">)
</CFQUERY>

Hope that helps!

[-__cfSearching__-]

insuractive wrote:

Just tried it and it looks like cfqueryparam list="yes" will clean up your list for you nicely:

Nice, tip.  I never realized it did list cleanup too. Yet another reason to like list="true" 😉