Posted by Plamen Ratchev on 02/06/07 22:36

Hi Paul,

There is a difference between NULL (or unknown value) and blank string (i.e.
''). If your column is NULL then DATALENGTH will return NULL, but if the
column is blank string then it will return 0.

As for your question if your query will always return zero records, that is
not correct. If you have a row where the DESCRIPTION column is a blank
string ('') then DATALENGTH will return 0 and since at the same time the
column is not NULL it will return it in the result set. Here is an example
to demonstrate this:

CREATE TABLE #Test (id int identity(1,1), description varchar(250) NULL)

INSERT INTO #Test (description) VALUES ('')
INSERT INTO #Test (description) VALUES (NULL)

SELECT id, description
FROM #Test
WHERE description IS NOT NULL
AND DATALENGTH(description) = 0

DROP TABLE #Test

The query above will return the first inserted row because it is not NULL
and the DATALENGTH of the blank string is 0.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]