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: Subject: Re: How to determine true contents of not null columns that display as empty From: Mark D Powell 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 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 s= pecified that made the customer think the column contains one or more space= s. I ran the following query (minus from/where) that returned results consi= stent with the customer view considering that datalength is supposed to ret= urn Null for Null. But when I added the ASCII function to see the value Nul= l returned. OK, so it appears that the column is being treated as a zero le= ngth 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 t= his? select coalesce(acceptor,'SPACE'),ISNULL(acceptor,'NULL'), LEN(accepto= r), DATALENGTH(acceptor), coalesce(DATALENGTH(acceptor),'X'), nullif(' ',ac= ceptor) "NULLIF", ASCII(acceptor) "ASCII", REPLACE(acceptor,'','X') "NOSPAC= E", REPLACE(acceptor,' ','X') "SPACE", UNICODE(acceptor) ... COALESCE ISNUL= L 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 e= arlier, but obvioulsy did not. So I was right and SQL Server is considerin= g the column an empty string, which is causing problems for my customer. T= he 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 pro= gramming construct and has no place in the database. Oh well, you need to work with a database based on how the database works s= o now to see if the application developers have figured out how to handle t= hese columns. Thanks again. Mark D Powell=20