CFLib.org – Common Function Library Project

Description:
This function takes a CF variable and optionally a CF datatype ('alpha' or 'numeric') and returns either the CF value or NULL. If it returns the CF value, it will be quoted if invoked with the 'alpha' datatype argument (default).

Return Values:
Returns a string.

Example:

<cfoutput>
<cfset testFld1 = "a">
update test<br>
set testfld = #nullColumn(testFld1)#<br>
where keycol = 2<br><br>
<cfset testFld2 = "">
update test<br>
set testfld = #nullColumn(testFld2)#<br>
where keycol = 2<br><br>
<cfset testFld3 = 9>
update test<br>
set testNumeric = #nullColumn(testFld3, 'numeric')#<br>
where keycol = 2<br><br>
</cfoutput>

Parameters:

Full UDF Source:

<cfscript>
/**
 * Useful in constructing SQL statements that must handle empty strings as NULLs.
 * Rewritten to use one UDF by RCamden
 * 
 * @param columnValue 	 The value to test.  (Required)
 * @param dataType 	 Allows you to specify 'alpha' or 'numeric'. If alpha, value is wrapped in single quotes. Default is alpha. (Optional)
 * @return Returns a string. 
 * @author Charles McElwee (&#99;&#109;&#99;&#101;&#108;&#119;&#101;&#101;&#64;&#101;&#116;&#101;&#99;&#104;&#115;&#111;&#108;&#117;&#116;&#105;&#111;&#110;&#115;&#46;&#99;&#111;&#109;) 
 * @version 1, September 20, 2002 
 */
function NullColumn(columnValue) {
	var dataType = "alpha";
	
	if(arrayLen(arguments) gte 2) dataType = arguments[2];
	if(trim(columnValue) eq "") return "NULL";
 	else if(dataType is "alpha") return "'" & columnValue & "'";
	else return columnValue;
}
</cfscript>