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'> ?
views:
743answers:
3
+10
A:
Null value will only be returned as empty string in a query object.
Henry
2009-10-29 18:02:42
+8
A:
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.
Peter Boughton
2009-10-29 18:22:48
Agreed. If you need to distinguish a true NULL value from an empty string, you need to do that in the query.
davidcl
2009-10-31 19:59:22
And sometimes I wish it didn't work like this. NULL can actually <i>mean</i> something in certain circumstances.
Ciaran Archer
2009-11-03 18:55:36
Yes, it means you've done your database wrong. ;)
Peter Boughton
2009-11-03 19:09:18
+1
A:
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.
np0x
2009-11-06 01:45:43