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

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

X-Received	by 10.224.189.78 with SMTP id dd14mr2227496qab.0.1363283811780; Thu, 14 Mar 2013 10:56:51 -0700 (PDT)
X-Received	by 10.49.29.99 with SMTP id j3mr311242qeh.38.1363283811551; Thu, 14 Mar 2013 10:56:51 -0700 (PDT)
Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder2.hal-mli.net!border3.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!t2no2813645qal.0!news-out.google.com!k8ni188qas.0!nntp.google.com!dd2no726226qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Thu, 14 Mar 2013 10:56:51 -0700 (PDT)
In-Reply-To	<60db4087-d8ae-4cbf-9a40-531637ccef0e@googlegroups.com>
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
References	<60db4087-d8ae-4cbf-9a40-531637ccef0e@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<d588d3fd-9c8f-4fc7-ad0a-914d78cf35b9@googlegroups.com> (permalink)
Subject	Re: How to determine true contents of not null columns that display as empty
From	Mark D Powell <Mark.Powell2@hp.com>
Injection-Date	Thu, 14 Mar 2013 17:56:51 +0000
Content-Type	text/plain; charset=ISO-8859-1
Content-Transfer-Encoding	quoted-printable
Lines	29
Xref	csiph.com comp.databases.ms-sqlserver:1411

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