why should I use Len(columnName) instead of columnName='' (sql server 2008)

Question

Just wondering I colleague said to me don't use columnName='' instead use Len(columnName)==0

This happened when I was querying something and I wanted all columns in my table where the value was null or empty.

Is there any benefit in using len i/o testing for empty string? I cannot see any.

thanks

Generally you don't want to be wrapping columns in function calls as this is unsargable and means that both indexes and column statistics can't be used. possible duplicate of [Is it faster to check if length = 0 than to compare it to an empty string?](http://stackoverflow.com/questions/3350959/is-it-faster-to-check-if-length-0-than-to-compare-it-to-an-empty-string) — Martin Smith, Sep 10 '11 at 07:19
@Martin Smith It seems like `Len` *could* be sargable though ... if it was chosen to be implemented as such an optimization (all the strings with the same length are consecutive in an index) so it could be transformed to (the intent-equivalent of) `name > "AAA" and name < "ZZZ"`. I have no idea if such an optimization is applied, or what the real-world performance impact of the indexes (if any) are though, just a musing. — , Sep 10 '11 at 07:52
@pst all the strings with the same length are not consecutive in an index, they are ordered lexicographically according to collation rules? — Martin Smith, Sep 10 '11 at 10:01
@Martin Smith Doh. You're absolutely correct. I'm not quite sure what I was thinking there... — , Sep 10 '11 at 12:16

score 1 · Accepted Answer · answered Sep 10 '11 at 07:20

1

If you want the rows where the value is empty string or null you should do like this.

where Col = '' or Col is null

Using len(Col) will not give you the the rows with null values.

answered Sep 10 '11 at 07:20

Mikael Eriksson

interesting .Didnt know that would not give row with null values – user9969 Sep 10 '11 at 07:25
i just saw this post and want to notice, that NULLs aren't values. NULL is just a marker for a missing or inapplicable value. But an empty string '' is a value. That's the difference. So when a column allows NULLs be sure you handle those in TSQL – CPMunich Aug 07 '15 at 15:43

1 Answers1