6 Replies Latest reply on Apr 27, 2009 3:35 PM by -==cfSearching==-

    ColdFusion List Question

    apocalipsis19 Level 1

      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.

        • 1. Re: ColdFusion List Question
          -==cfSearching==- Level 4

          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.

          • 2. Re: ColdFusion List Question
            sanman Level 1

            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'

            • 3. Re: ColdFusion List Question
              -==cfSearching==- Level 4

               

              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)).

              • 4. Re: ColdFusion List Question
                insuractive Level 3

                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.

                • 5. Re: ColdFusion List Question
                  insuractive Level 3

                  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!

                  • 6. Re: ColdFusion List Question
                    -==cfSearching==- Level 4

                    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" ;-)