|
|
Posted by Erland Sommarskog on 02/06/07 22:53
Paul (paulwragg2323@hotmail.com) writes:
> If I have a column DESCRIPTION VARCHAR(250) and the value is empty
> i.e. NULL then
> shouldn't doing a:
>
> SELECT TAB1.DESCRIPTION
> FROM TABLE1 TAB1
> WHERE TAB1.DESCRIPTION IS NOT NULL
> AND DATALENGTH(TAB1.DESCRIPTION) = 0
>
> always return zero records?
> A VARCHAR is variable length based on the actual data stored in it, so
> shouldn't a VARCHAR column of zero DATALENGTH be NULL?
No:
DECLARE @x varchar(234)
SELECT @x = ''
SELECT isnull = CASE WHEN @x IS NULL THEN 1 ELSE 0 END,
bytes = datalength(@x)
NULL and the empty string are two different values. Or maybe they the
same two values. After all, NULL is the unknown value, so you don't
know what it is. The empty string, on the other hamd, is a known value.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|