SQL Server 2008 - how to check if a TEXT field is NULL?

[TheBugSlayer]

Hi.

I am getting apparently inconsistent results while trying to discard rows where a column with TEXT datatype has a null value.

Query 1

[b]
 SELECT C.Number AS CaseNumber, 
        ST.ObjectID AS SpecimenID,
        ST.Gross ,
        DataLength(ST.Gross)
 FROM dbo.Case C WITH (NOLOCK) 
 JOIN dbo.Specimen ST ON ST.[CASE] = C.ObjectID
 WHERE C.Number like 'C%' or C.Number like 'F%' or C.Number like 'N%' or C.Number like 'S%' AND 
 DATALENGTH(ST.Gross) > 0 -- OR DATALENGTH(ST.Gross) IS NOT NULL
[/b]

returns 214 rows, most with Length > 0 but some have a length of NULL!

I tried a few permutations of the WHERE clause and no matter which one I use, there are rows with null length.

The database is in SQL Server 2008 but I am using Quest' s Toad for SQL Server to query it as my SQL Server 2005 SSMS does not allow me to connect to SQL Server 2008 databases.

Am I missing something? Any help is appreciated.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Your OR's are killing you. Whenever you mix and's with or's in the where clause, you should use parenthesis to control the logic, like this...

WHERE [!]([/!]C.Number like 'C%' 
      or C.Number like 'F%' 
      or C.Number like 'N%' 
      or C.Number like 'S%'[!])[/!]
      AND DATALENGTH(ST.Gross) > 0

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

You could simplify this even more (to remove the OR's), like this...

WHERE C.Number like '[CFNS]%'
      AND DATALENGTH(ST.Gross) > 0

I don't this this will cause your query to perform any better, but I find this method easier to read. Basically... "where the first character of the number column is c, f, n, or s".

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Ah, typical case of looking hard without seeing! I am embarrassed. . This is a LINQ query that I copied from my C# app to test in SQL Server but I messed up the syntax in the process. Thank you both. George, I love that syntax but my fellows here complain about anything remotely complex, or new for that matter!

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I would argue that the 2nd version is less complex than 4 separate OR conditions. Regardless, I'm glad I was able to help.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

You actually posted all answers. My attention span is really low this afternoon. Thanks again.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)