June 18, 2012 at 9:28 am
I apologize if this is not the right place for this question. I couldn't seem to find anything close.
I'm troubleshooting a stored proc and came across this syntax:
AND ISNULL(p.TrackingNumber,'') > ''
This code dates back to SQL2000. I normally compare nulls like this to = '' or <> ''. I've never seen syntax for > '' (greater than blank). I don't know if this is a typeo or if it is correct syntax. If it is correct, someone please tell me what IS greater than a blank-- anything?
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
June 18, 2012 at 9:39 am
Caruncles (6/18/2012)
I apologize if this is not the right place for this question. I couldn't seem to find anything close.I'm troubleshooting a stored proc and came across this syntax:
AND ISNULL(p.TrackingNumber,'') > ''
This code dates back to SQL2000. I normally compare nulls like this to = '' or <> ''. I've never seen syntax for > '' (greater than blank). I don't know if this is a typeo or if it is correct syntax. If it is correct, someone please tell me what IS greater than a blank-- anything?
First, the '' is not a blank, it is the empty string. If p.TrackingNumber is null, it is replaced with the empty string. By making the comparision greater than the empty string, any records where p.TrackingNumber is null will be excluded from the result set.
June 18, 2012 at 9:44 am
I'd never seen > used against a string, but I have tried a couple of queries and do get results. I just wasn't sure if I was getting the correct results.
Thanx for the confirmation!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
June 18, 2012 at 9:46 am
Any character is greater then an empty string. Most chances are that the developer that wrote this statement wanted to get back all the values that that don't have an empty string and also don't have null.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 19, 2012 at 4:41 am
The code can be optimized if rewritten as:
AND p.TrackingNumber > ''
Since NULL will not be greater than anything the call to ISNULL is wasteful.
edit: put code inside quote block to get rid of amp gt; notation site imposed
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 6:26 am
Thanx OPC.THree.
I normally write it as:
" AND isnull(p.TrackingNumber,'') <> '' "
Though you are right. Neither NULLs or blanks should be returned in this query.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
June 20, 2012 at 8:09 pm
Without seeing the whole conditional, it's hard to tell if the ISNULL is a waste because NULL is not a value. E.g.,
WHERE ship_by_date > @today
OR ( pick_up_date IS NOT NULL
AND ISNULL(p.TrackingNumber,'') > ''
)
David Lathrop
DBA
WA Dept of Health
June 21, 2012 at 4:24 am
Wallace Houston, Instead of " AND isnull(p.TrackingNumber,'') <> '' "
I would sugguest to use
AND p.TrackingNumber=p.TrackingNumber
this code will ignore all NULL values and also works for all datatypes.
June 21, 2012 at 6:09 am
DLathrop (6/20/2012)
Without seeing the whole conditional, it's hard to tell if the ISNULL is a waste because NULL is not a value. E.g.,WHERE ship_by_date > @today
OR ( pick_up_date IS NOT NULL
AND ISNULL(p.TrackingNumber,'') > ''
)
Can you clarify what you mean? As far as I know this:
ISNULL(p.TrackingNumber,'') > ''
is logically equivalent in all cases to this
p.TrackingNumber > ''
making ISNULL wasteful.
For this thread I am assuming p.TrackingNumber to be a character type because the example compares it to a string literal. For rows where p.TrackingNumber is NULL the first statement explicitly casts NULL to an empty string using ISNULL and then compares as greater-than to an empty string, which will be false since NULL becomes '' which is not greater-than ''. For rows where p.TrackingNumber is NULL the second statement does not explicitly cast NULL to anything, so NULL is compared as greater-than to an empty string which will also be false since NULL will not satisfy any predicate using a comparison operator.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2012 at 7:31 am
ISNULL(p.TrackingNumber,'') > ''
...may be wasteful in terms of processor cycles, but it makes it a bit clearer that the author was at least deliberately thinking about the NULL case. That seems like a good thing.
June 21, 2012 at 7:44 am
dmbaker (6/21/2012)
ISNULL(p.TrackingNumber,'') > ''...may be wasteful in terms of processor cycles, but it makes it a bit clearer that the author was at least deliberately thinking about the NULL case. That seems like a good thing.
That's a fair point, but I think a comment would be a better way for the developer to show their line of thinking. By adding ISNULL not only is the query wasting processor cycles by running the function for every row returned in the FROM clause, it is also potentially negating the use of an index on the column by having wrapped it with a function.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2012 at 10:37 am
Without seeing the whole conditional, it's hard to tell if the ISNULL is a waste because NULL is not a value. E.g.,
WHERE ship_by_date > @today
OR ( pick_up_date IS NOT NULL
AND ISNULL(p.TrackingNumber,'') > ''
)
Sorry, poor example. I meant to point out that there is a difference between AND FALSE (which is FALSE) and AND NULL (which is NULL). However in my example the difference between OR FALSE and OR NULL is irrelevant. In other situations, this difference can lead to unexpected results.
Of course, if p.TrackingNumber is defined with NOT NULL, any form of NULL checking is a waste of time. (I don't know if the query optimizer will remove these or not.)
This is another example that just posting code fragments without any context leaves a lot of room for " but then again ..." responses (mine included).
David Lathrop
DBA
WA Dept of Health
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply