Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by VM » not like?
Add to briefcase
22 posts, Page 1 of 3
123»»»

not like? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
VM-723206
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: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263
Comments posted to this topic are about the item not like?
Post #680956
Ronald H
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: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602
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
mtassin
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: Today @ 4:19 AM
Points: 3,231, Visits: 64,348
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
Lynn Pettis
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 @ 2:45 AM
Points: 21,630, Visits: 27,485
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
tkruse
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 09, 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
kevin.l.williams
Posted Monday, March 23, 2009 9:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:52 AM
Points: 2,796, Visits: 1,125
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
mosaic-263591
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, May 22, 2013 7:37 AM
Points: 419, Visits: 556
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
Lynn Pettis
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 @ 2:45 AM
Points: 21,630, Visits: 27,485
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
kevin.l.williams
Posted Monday, March 23, 2009 10:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:52 AM
Points: 2,796, Visits: 1,125
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
john.arnott
Posted Monday, March 23, 2009 10:25 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
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
22 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.