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

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

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

Show all headers | View raw

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


  

Back to comp.databases.ms-sqlserver | Previous | Next — Next 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