Reputation: 1644
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
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
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
Reputation: 2378
It would be safer to use the official syntax 'IS NULL' OR 'IS NOT NULL'
Upvotes: 0