Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1411

Re: How to determine true contents of not null columns that display as empty

Show all headers | View raw

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(acceptor,'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 
  

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

How to determine true contents of not null columns that display as empty Mark D Powell <Mark.Powell2@hp.com> - 2013-03-07 09:16 -0800
  Re: How to determine true contents of not null columns that display as empty rja.carnegie@gmail.com - 2013-03-07 09:29 -0800
  Re: How to determine true contents of not null columns that display as empty Erland Sommarskog <esquel@sommarskog.se> - 2013-03-07 22:55 +0100
    Re: How to determine true contents of not null columns that display as empty rja.carnegie@gmail.com - 2013-03-07 15:06 -0800
  Re: How to determine true contents of not null columns that display as empty Mark D Powell <Mark.Powell2@hp.com> - 2013-03-14 10:56 -0700

csiph-web