CF_ListQualify

Places single quote marks or some other "qualifier" around each element in a CF-style list. Similar to the CF built-in QuotedValueList function, except can be used on lists rather than only on query columns. Useful when constructing a dynamic SQL statement when specifying criteria for a character-type column with the IN keyword.

LIST	Required. The list that you want to qualify. For instance, LIST="Red,White,Blue" or LIST="#MyList#"
VARIABLE	Optional. A valid variable name to put the finished information into. Defaults to ListQualified. So after CF_ListQualify runs, the qualified version of LIST will be available as #ListQualified# for your use, unless you specify a different VARIABLE name here.
QUALIFIER	Optional. The character to put around each element in LIST. Defaults to the single quote character ('), so Red,White,Blue would become 'Red','White','Blue' unless you specify a different QUALIFIER here. QUALIFIER is also stripped from each element of the original list to avoid problems. So Red's,White's,Blue's would become 'Red','White','Blue'.
DELIMITER	Optional. Defaults to a comma (,). Just as with the CF built-in list functions, LIST can be delimited by a character other than the comma. So if LIST was Red\|White\|Blue you would probably want to use DELIMITER="\|" to use this tag.
SKIPVALUE	Optional. Defaults to none. If this value is encountered in the list, it will not be qualified. So if you specified SKIPVALUE="null", then Red,null,Blue would become 'Red',null,'Blue'.

Example

<CFSET MyList = "Red,White,Blue"> <CF_ListQualify LIST="#MyList#"> <CFQUERY NAME="MyQuery" DATASOURCE="MyDS"> SELECT * FROM MyTable WHERE MyColumn IN (#PreserveSingleQuotes(ListQualified)#) </CFQUERY> The resulting SQL statement would be

  SELECT * FROM MyTable
  WHERE MyColumn IN ('Red','White','Blue')