SQL "IN" Clauses, and CF Single-Quotes

I firmly believe that problems can always teach you something, it's sometimes just not that fun of a "learning process." So last week I had this really annoying problem trying to pass a comma-delimited list to a SQL "IN" clause. In the past I've solved the dynamic IN clause issue with code that looks something like this:


AND (

<cfloop list="#myList#" delimiters="," index="i">

(myTable.myCol = '#i#')

<cfif i IS NOT ListLast(#myList#)>

OR

</cfif>

</cfloop>

)


I always felt it was kind of ugly but never bothered to learn a better way. I should have looked it up sooner! I knew I could use an IN clause but the problem was wrapping the IN clause list with quotes. Doing that manually seemed worse than just looping over the list. The real problem with the above code arose when the list contained multiple list items of the same value. In that case, the ListLast function did not produce the desired result, and the resulting SQL was invalid.

I started out looking for some type of function that would remove duplicates from a list. I then stumbled across ListQualify(list, wrap character, delimiter, scope), which turned out to be a super-handy function. Pass it a list, a character, a delimiter and a scope and it will wrap each list item in the desired character - you can even choose to only wrap characters, or all list items. So I could wrap my list values in single quotes, and then use the SQL IN clause. Yay!

As a side note, I ran into a very annoying but rather funny issue with CFEclipse's auto-correct feature. I dare you to try typing this string in CFEclipse: "'" (that's double-quote, single-quote, double-quote). Yeah, not happening. I had to type it in Notepad and then paste it in. Sometimes you just feel like the world is against you!

I was ready for that ugly error to finally be gone when I refreshed the page, but lo and behold, a new error. Apparently each single quote mark in my list was being doubled. I did not know that CF automatically escapes these characters in such a situation. That's when Adam reminded me of the PreserveSingleQuotes() function, which does just what it sounds like, and viola, no more error.

In addition to learning something new, it's sure nice to have someone who knows everything about CF right at home!