Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

not like? Expand / Collapse
Author
Message
Posted Saturday, March 21, 2009 11:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item not like?
Post #680956
Posted Monday, March 23, 2009 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today :)

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Post #681450
Posted Monday, March 23, 2009 8:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
r.hensbergen (3/23/2009)
I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today :)


Actually,

They should use COALESCE, it's much easier to use, doesn't request nesting for multiple tests and is ANSI SQL compliant.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #681499
Posted Monday, March 23, 2009 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 23,397, Visits: 32,238
mtassin (3/23/2009)
r.hensbergen (3/23/2009)
I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today :)


Actually,

They should use COALESCE, it's much easier to use, doesn't request nesting for multiple tests and is ANSI SQL compliant.


ISNULL may not be ANSI SQL compliant, but I think you will find it faster than COALESCE when you only need to test a single value for a null value. Using COALESCE makes sense when you have multiple values and you want the first non-null value.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #681582
Posted Monday, March 23, 2009 9:56 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 9, 2010 8:19 AM
Points: 81, Visits: 90
To Null Or Not To Null, but let's be consistent.

This is usually hard to explain to a non techinical person who knows enough SQL to be dangerous.

http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/




Post #681593
Posted Monday, March 23, 2009 9:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:39 AM
Points: 3,142, Visits: 1,260
I know why the null value is not returned but I am puzzled by the explanation.

"Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."



Post #681594
Posted Monday, March 23, 2009 10:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:18 AM
Points: 419, Visits: 559
This happens to be one of my eternal blind spots.
I have lost count of the number of times I had to correct a query because I forgot that using 'NOT LIKE' also implies "NOT NULL".
The error may go unnoticed for ages because missing a few rows from the query results is not easy to spot.
Thanks for the reminder ... but no doubt I'll make the same mistake again - sigh.
Post #681607
Posted Monday, March 23, 2009 10:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 23,397, Visits: 32,238
kevin.l.williams (3/23/2009)
I know why the null value is not returned but I am puzzled by the explanation.

"Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."


It works like this, the records returned by the following query:

select col1 from @test where col2 not like '%del%'

are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

Try the following:

declare @TestVal int;
set @TestVal = null; -- ensure @TestVal is null
select
case when @TestVal = null then 'equal null'
when @TestVal <> null then 'not equal null'
else 'undetermined'
end;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #681615
Posted Monday, March 23, 2009 10:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:39 AM
Points: 3,142, Visits: 1,260
Lynn Pettis (3/23/2009)
kevin.l.williams (3/23/2009)
I know why the null value is not returned but I am puzzled by the explanation.

"Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."


It works like this, the records returned by the following query:

select col1 from @test where col2 not like '%del%'

are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

Try the following:

declare @TestVal int;
set @TestVal = null; -- ensure @TestVal is null
select
case when @TestVal = null then 'equal null'
when @TestVal <> null then 'not equal null'
else 'undetermined'
end;



Understood. I just don't think the authors explanation will help anyone who does not understand why 4 is not returned.



Post #681619
Posted Monday, March 23, 2009 10:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
What's happening here is that comparisons with NULL always return "false". Not even NULL compares true to NULL. So, even though NULL (row 4) doesn't contain string "del", it still gives a "false" when compared with (like) '%del%'. Null means no value is assigned and you may not compare any value to an undetermined value.

Comparisons with NULL return "false" whether you use equal (=) or not equal (!=), or use "like" or "not like". This code...
If 'Hello' = NULL print 'Hello = Null' 
else print 'Hello != NULL'
If 'Hello' != NULL print 'Hello != Null'
else print 'Hello = NULL'

returns the rather schizophrenic results....
Hello != NULL
Hello = NULL

So, to cover yourself, you can explicitly code for the possibility of NULL with either "is NULL" or one of the operators "IsNull" or "Coalesce". like mtassin in previous post, I prefer coalesce.
Post #681626
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse