February 15, 2010 at 12:34 pm
For the queries below, the first one returns nothing, but the second one returns NULL
Why does the first one return nothing. Can someone provide an explanation, or the location to such in the BOL, so that I can read more about this.
--Returns Nothing
DECLARE @FirstName VARCHAR(50)
SET @FirstName = NULL
IF @FirstName IS NULL OR UPPER(@FirstName) NOT LIKE '%And%' AND UPPER(@FirstName) NOT LIKE '%Jim%'
SELECT @FirstName
GO
--Returns NULL
DECLARE @FirstName VARCHAR(255)
SET @FirstName = 'Andrew'
IF UPPER(@FirstName) NOT LIKE '%And%' AND UPPER(@FirstName) NOT LIKE '%Jim%'
SELECT @FirstName
GO
Thanks
Andrew SQLDBA
February 15, 2010 at 12:47 pm
Speculation on my part: are your conjunctions in your IF statement what you want?
For example, you don't have any parentheses. I think it's interpreted as "if x OR (y AND z)", but how do we know you don't want "if (x OR y) AND z"? (I think "AND" takes precendence over "OR" -- someone correct me if I'm wrong.)
Don't know if that's it, but that's something I'd look at.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 15, 2010 at 2:00 pm
Your conclusions are actually backwards. The first one returns NULL, the second one returns nothing. Run them separately.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2010 at 2:19 pm
It is not that it is not at all working, or there is not a syntax error, but my questions is this. What make this one return nothing
--Return nothing, not even Null.
-- only shows 'Command Completed Successfully'
-- 1
DECLARE @WorkSheetName VARCHAR(255)
SET @WorkSheetName = NULL
IF UPPER(@WorkSheetName) NOT LIKE '%SUBEVENT%' AND UPPER(@WorkSheetName) NOT LIKE '%PBVIEW%'
SELECT @WorkSheetName
This one shows the value of 'Test'
-- 2
DECLARE @WorkSheetName VARCHAR(255)
SET @WorkSheetName = 'TEST'
IF UPPER(@WorkSheetName) NOT LIKE '%SUBEVENT%' AND UPPER(@WorkSheetName) NOT LIKE '%PBVIEW%'
SELECT @WorkSheetName
And this one returns NULL
-- 3
DECLARE @WorkSheetName VARCHAR(255)
SET @WorkSheetName = NULL
IF @WorkSheetName IS NULL OR UPPER(@WorkSheetName) LIKE '%COLLECTION%'
SELECT @WorkSheetName
Why does number 1 return nothing what so ever. I think it should have returned NULL. I am only wanting to understand why nothing at all was returned. I can find nothing in the SQL BOL as to the reasoning.
Thanks
Andrew SQLDBA
February 15, 2010 at 2:57 pm
The joys of null!
NULL is not a value, so as such, NULL is neither equal nor not equal to any value. Unfortunately, that also means that it's not LIKE or not NOT LIKE any value either.
As such - the condition in query 1 fails (or returns something other than TRUE, since it evaluates to UNKNOWN, or NULL), the select doesnt' fire.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2010 at 3:55 pm
Thanks Matt
Appreciate your explanation. You know, some things in SQL are sometimes difficult to explain, although you know the answer or how it works. It is at least for me.
Andrew SQLDBA
February 17, 2010 at 5:17 am
To understand this better Michael Coles has written a couple of excellent articles, the second of which is here: http://www.sqlservercentral.com/articles/Advanced+Querying/2829/
February 18, 2010 at 10:06 am
Matt Miller (#4) (2/15/2010)
The joys of null!NULL is not a value, so as such, NULL is neither equal nor not equal to any value. Unfortunately, that also means that it's not LIKE or not NOT LIKE any value either.
As such - the condition in query 1 fails (or returns something other than TRUE, since it evaluates to UNKNOWN, or NULL), the select doesnt' fire.
This is not true. You can compare nulls by setting ANSI_NULLS to OFF.
The most likely reason the LIKE operator fails for NULL values even with "SET ANSI_NULLS OFF" is due to the nature of the LIKE operator. Syntactically, you cannot check whether an object is LIKE NULL because the pattern is required to be in quotes. You cannot issue the following command...
IF @abc LIKE NULL
PRINT '@abc is null'
So the LIKE operator syntactically cannot compare NULLs. And as result, the LIKE/NOT LIKE operators returns UNKNOWN whenever @abc is NULL.
It appears that you indeed can issue "IF @abc LIKE NULL ... ". Then in my opinion, the LIKE operator should be able to compare nulls when ANSI_NULLS are OFF.
February 20, 2010 at 5:03 am
nadabadan
This is not true. You can compare nulls by setting ANSI_NULLS to OFF.
But you definitely shouldn't! 😀
Setting ANSI_NULLS OFF can produce some very counter-intuitive behaviour. No wonder that it is deprecated and strongly discouraged, see Books Online SET ANSI_NULLS. An extract:
Paul
February 20, 2010 at 9:26 am
nadabadan (2/18/2010)
Matt Miller (#4) (2/15/2010)
The joys of null!NULL is not a value, so as such, NULL is neither equal nor not equal to any value. Unfortunately, that also means that it's not LIKE or not NOT LIKE any value either.
As such - the condition in query 1 fails (or returns something other than TRUE, since it evaluates to UNKNOWN, or NULL), the select doesnt' fire.
This is not true. You can compare nulls by setting ANSI_NULLS to OFF.
The most likely reason the LIKE operator fails for NULL values even with "SET ANSI_NULLS OFF" is due to the nature of the LIKE operator. Syntactically, you cannot check whether an object is LIKE NULL because the pattern is required to be in quotes. You cannot issue the following command...
IF @abc LIKE NULL
PRINT '@abc is null'
So the LIKE operator syntactically cannot compare NULLs. And as result, the LIKE/NOT LIKE operators returns UNKNOWN whenever @abc is NULL.
It appears that you indeed can issue "IF @abc LIKE NULL ... ". Then in my opinion, the LIKE operator should be able to compare nulls when ANSI_NULLS are OFF.
Actually - it IS true. What you're allowed to do when ANSI_NULLS is turned off is use =NULL or <>NULL instead of IS NULL or IS NOT NULL. The back end pieces don't change: you're not checking values (since there isn't one), you're still checking the STATE and returning those with the correct state. All this is is a syntax trick (which is essentially why it's going again in the next release).
Like fails because the pattern requires checking against the VALUE.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2010 at 11:32 pm
Matt Miller (#4) (2/15/2010)
NULL is not a value, so as such, NULL is neither equal nor not equal to any value. Unfortunately, that also means that it's not LIKE or not NOT LIKE any value either.
This is a true statement when ANSI_NULLS is ON.
Matt Miller (#4) (2/15/2010)
As such - the condition in query 1 fails (or returns something other than TRUE, since it evaluates to UNKNOWN, or NULL), the select doesnt' fire.
The result of a LIKE comparison cannot return UNKNOWN, since LIKE is one of the few T-SQL constructs that returns a Boolean (EXISTS is another one). LIKE can therefore only ever return TRUE or FALSE.
Paul
February 21, 2010 at 11:32 am
Paul White (2/20/2010)
Matt Miller (#4) (2/15/2010)
NULL is not a value, so as such, NULL is neither equal nor not equal to any value. Unfortunately, that also means that it's not LIKE or not NOT LIKE any value either.This is a true statement when ANSI_NULLS is ON.
Matt Miller (#4) (2/15/2010)
As such - the condition in query 1 fails (or returns something other than TRUE, since it evaluates to UNKNOWN, or NULL), the select doesnt' fire.The result of a LIKE comparison cannot return UNKNOWN, since LIKE is one of the few T-SQL constructs that returns a Boolean (EXISTS is another one). LIKE can therefore only ever return TRUE or FALSE.
Paul
Paul - if you look at what actually happens - you will see that all it did was to enable a syntax difference.
Try this one:
set ansi_nulls off
if null=null
print 'true1'
if (null + null = null*null) --if truly a value
print 'true 2'
if null like null
print 'true 3'
if '' not like null
print 'true 4'
if '' not like null
print 'true 5'
It's not changing the definition of null at all, so NULL still isn't being treated as a value (by the way, as unfortunately as it may be, the BOL article is lacking: anything dealing with null going around talking about "null values" unfortunately shows a lack of basic understanding of the concept).
It's allowing for the notation to be used, but the handling is STILL going to be IS NULL or IS NOT NULL.
As to boolean, there are lots of settings where boolean statements can return UNKNOWN (or NULL) here, so I'm not sure how calling it a boolean automatically restricts it to TRUE/FALSE. Not sure how we'd go about proving it one way or another, so it's probably not worth belaboring however.
That said - even with ANSI_NULLS off - the LIKE syntax still won't work with NULLs.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2010 at 11:41 am
Sorry - just remembered how Michael Coles did it.
LIKE unfortunately can returning an UNKNOWN:
set ansi_nulls off
if null like null
print 'true 3'
if not (null like null)
print 'true 3a'
if '' not like null
print 'true 4'
if '' like null
print 'true 4a'
if LIKE were only returning true/false, two of those statement should be printing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2010 at 7:24 pm
Matt,
:blush:
My apologies - I misremembered how LIKE works and should have checked it in code rather than just looking at its return type in BOL.
So sorry about that: LIKE does indeed work with three-state logic.
To be clear, you're absolutely right, for the case involving LIKE.
:blush:
Without squirming out of anything, however, I'd like to try to re-make my point about SET ANSI_NULLS.
I'll do that in a separate post, to keep things clear.
Paul
February 21, 2010 at 8:07 pm
Matt Miller (#4) (2/15/2010)
NULL is not a value, so as such, NULL is neither equal nor not equal to any value.
So, let me try again :w00t:
This above quoted statement is the part that I intended to examine. I'm quite embarrassed about the my confusion over LIKE, but I'll try to get over it...
SET ANSI_NULLS OFF can cause some quite unexpected behaviour, unless you can remember all the rules.
Essentially, when ANSI_NULLS is OFF, equality comparisons (using = or <>) return TRUE instead of UNKNOWN when comparing two NULLs, so long as at least one of the compared operands is a variable or literal NULL.
A subtlety is that the change in behaviour also affects IN, since IN is equivalent to '= ANY'. There are some decidedly counter-intuitive aspects to using IN with NULLs. Since each comparison made to test the IN involves an equality comparison, IN can appear to break the rule stating that one of the operands has to be a variable or literal NULL.
Consider the following statement, which returns no output at all if ANSI_NULLS is ON. If ANSI_NULLS is OFF, only tests 5, 7, and 10 fail to return a row...
SET ANSI_NULLS OFF;
-- Create a variable containing NULL
DECLARE @NULL INTEGER;
SET @NULL = NULL;
-- A table with a single NULLable column
DECLARE @NullTable
TABLE (value INTEGER NULL);
-- Add one row, conatining NULL
INSERT @NullTable(value) VALUES (NULL);
-- Returns a row
SELECT 1
WHERE NULL = NULL
UNION ALL
-- Returns a row
SELECT 2
WHERE @NULL = (SELECT NULLIF(1, 1))
UNION ALL
-- Returns a row
SELECT 3
WHERE @NULL IN (1, 2, 3, NULL)
UNION ALL
-- Returns a row
SELECT 4
WHERE @NULL = (NULL)
UNION ALL
-- Does NOT return a row
SELECT 5
WHERE (SELECT NULL) = (SELECT NULL)
UNION ALL
-- Returns a row
SELECT 6
WHERE (SELECT NULL) IN (SELECT NULL)
UNION ALL
-- Does NOT return a row
SELECT 7
WHERE (SELECT @NULL) = (SELECT NULL)
UNION ALL
-- Returns a row
SELECT 8
FROM @NullTable
WHERE value = NULL
UNION ALL
-- Returns a row
SELECT 9
FROM @NullTable
WHERE value * value = NULL
UNION ALL
-- Does NOT return a row
SELECT 10
FROM @NullTable
WHERE value * value = NULLIF(1, 1)
UNION ALL
-- Returns a row
SELECT 11
FROM @NullTable
WHERE (value * value) + value = ANY (SELECT NULLIF(1, 1));
Paul
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply