CFLib.org – Common Function Library Project

NullColumn(columnValue[, dataType])

Last updated September 20, 2002

author

Charles McElwee

Version: 1 | Requires: CF5 | Library: DatabaseLib

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:

Name Description Required
columnValue The value to test. Yes
dataType Allows you to specify 'alpha' or 'numeric'. If alpha, value is wrapped in single quotes. Default is alpha. No

Full UDF Source:

/**
 * 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 (cmcelwee@etechsolutions.com) 
 * @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;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson