From: Mark.Powell2@hp.com   
      
   On Thursday, March 7, 2013 12:16:48 PM UTC-5, Mark D Powell wrote:   
   > We have a table with a column that shows it must hold a value (nvarchar(7)   
   not null), but when displayed always shows nothing. Since "not null" is   
   specified that made the customer think the column contains one or more spaces.   
   I ran the following query (   
   minus from/where) that returned results consistent with the customer view   
   considering that datalength is supposed to return Null for Null. But when I   
   added the ASCII function to see the value Null returned. OK, so it appears   
   that the column is being    
   treated as a zero length string. How can a "not null" colummn be empty? Can   
   anyone explain what I am seeing and where specifically in the documentation I   
   can read up on this? select coalesce(acceptor,'SPACE'),ISNULL(ac   
   eptor,'NULL'), LEN(acceptor),    
   DATALENGTH(acceptor), coalesce(DATALENGTH(acceptor),'X'), nullif(' ',acceptor)   
   "NULLIF", ASCII(acceptor) "ASCII", REPLACE(acceptor,'','X') "NOSPACE",   
   REPLACE(acceptor,' ','X') "SPACE", UNICODE(acceptor) ... COALESCE ISNULL LEN   
   DATALENGTH CK_DATAL NULLIF    
   ASCII NOSPACE SPACE UNICODE ........ ...... 0 0 0 NULL NULL ....... ..... NULL   
   Curious. -- Mark D Powell --   
      
   rja and Erland, thank you for the responses.  I had thought I had replied   
   earlier, but obvioulsy did not.  So I was right and SQL Server is considering   
   the column an empty string, which is causing problems for my customer.  The   
   way I see it varchar is    
   variable length character data and if the length is zero you have no data   
   which means the value is NULL.  A string is a programming construct and has no   
   place in the database.   
      
   Oh well, you need to work with a database based on how the database works so   
   now to see if the application developers have figured out how to handle these   
   columns.   
      
   Thanks again.  Mark D Powell    
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)