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

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

X-Received	by 10.224.17.140 with SMTP id s12mr27614587qaa.3.1362676608826; Thu, 07 Mar 2013 09:16:48 -0800 (PST)
X-Received	by 10.49.24.13 with SMTP id q13mr3486348qef.33.1362676608519; Thu, 07 Mar 2013 09:16:48 -0800 (PST)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!dd2no8040382qab.0!news-out.google.com!p7ni382qai.0!nntp.google.com!dd2no8040378qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Thu, 7 Mar 2013 09:16:48 -0800 (PST)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=15.227.185.76; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u
NNTP-Posting-Host	15.227.185.76
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<60db4087-d8ae-4cbf-9a40-531637ccef0e@googlegroups.com> (permalink)
Subject	How to determine true contents of not null columns that display as empty
From	Mark D Powell <Mark.Powell2@hp.com>
Injection-Date	Thu, 07 Mar 2013 17:16:48 +0000
Content-Type	text/plain; charset=ISO-8859-1
Content-Transfer-Encoding	quoted-printable
Xref	csiph.com comp.databases.ms-sqlserver:1405

Show key headers only | 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