SQL query not returning nulls RRS feed

  • Question

  • Is there a way to set SQL Server 2005 Express so that I can return null values?  For example, the following query will not return any values: 


    SELECT *

    FROM tbl_form_values

    where fldVALUE IS NULL;


    it does return values with:


    SELECT *

    FROM tbl_form_values

    where fldVALUE = '';


    I have an Oracle background and all null values are true nulls not empty strings.  I would like to be able to use the first query.  Also other functions such as COALESCE work very nice with nulls.  I can do the following in Oracle but not in SQL Server:


    SELECT fldID, fldMID, fldFID,

                 COALESCE(fldVALUE, 'n/a');

    FROM tbl_form_values


    This will return the values in fldVALUE if they are available and n/a for all NULL values.


    Any help would be appreciated.




    Friday, September 21, 2007 2:14 AM


  • the problem here is the column is not having null value thats why its not returning.  In sql server ' ' is nothing but Char(0). In this particular column char(0) value is stored not null. This is a very common mistake what application developers do.


    so either change the query like this or update all ' ' to Null


    SELECT *

    FROM tbl_form_values

    where len(fldVALUE)=0




    Friday, September 21, 2007 10:55 AM