locked
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.

     

    Thanks,

    E

    Friday, September 21, 2007 2:14 AM

Answers

  • 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

     

    Madhu

     

    Friday, September 21, 2007 10:55 AM