CODEWITHSUNDEEP
sqlsql-servert-sql
userSteve
userSteve

Reputation: 1644

Will this null comparison always produce same result?

I am testing if a varchar field is not null or empty. Performing the test using [field] <>'' seems to work fine including if the value is NULL.

But are there scenarios where this may not work as expected, eg different collation or ANSI NULL settings?

Would I be safer to use [field] IS NOT NULL AND [field]<>'' ?

Upvotes: 1

Views: 62

Answers (4)

Chithra
Chithra

Reputation: 1

You can check below script:

ISNULL([field], '') <> ''

Upvotes: 0

GuidoG
GuidoG

Reputation: 12059

for a varchar field the check 

[field] IS NOT NULL AND [field]<>''

can be done shorter like this

ISNULL([field], '') <> ''

They do exact the same thing

But as Pred mentioned in his comment, it could have negative impact on performance so the answer of Sean is better

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

There really is no need for an inequality check here at all (remember that <> is nonSARGable). Since you want the rows that are NOT NULL and not an empty string you can simplify this and make your predicates SARGable.

[field] > ''

Upvotes: 5

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

It would be safer to use the official syntax 'IS NULL' OR 'IS NOT NULL'

Upvotes: 0

Related Questions