How do I handle null values from ColdFusion queries?

Question

If one of the columns in the returned coldfusion query result set has a NULL, how do we check if the value of this column being NULL?

Should we just say <cfif queryname.columnname[i] EQ ''> OR <cfif queryname.columnname[i] eq 'NULL'> ?

Solution 1

Null value will only be returned as empty string in a query object.

Solution 2

Ideally, you would use your database's ifnull (or similar) method to convert null values to something appropriate before they reach CF.

If not, they come back as an empty string, so your first cfif is correct.

Solution 3

If you are using an oracle database you could use the NVL method and modify your query to wrap the column.

<cfset var nullstringvalue = "THELASTNAMEFIELDISNULL" />
<cfquery name="lastnamelookup" ....>
  SELECT NVL(lastname, 'LASTNAMEISNULL') AS lastname
</cfquery>
<cfif lastnamelookup.lastname EQ nullstringvalue>
     ...do null case work...
</cfif>

The trick would be in determining the correct bogus string to use in this case, then testing for it.

Comments

[davidcl]

Agreed. If you need to distinguish a true NULL value from an empty string, you need to do that in the query.

[Ciaran Archer]

And sometimes I wish it didn't work like this. NULL can actually <i>mean</i> something in certain circumstances.