Post reply

LIKE (T-SQL)very strange

  • October 23, 2008 at 11:43 am

    #96758

    I am pulling two columns from a table FIRSTNAME and EMAIL and below are the resultset and my query

    select firstname,email

    from gsiftp.dbo.wrk_prospects where email like '%BK@cast%'

    FIRSTNAME EMAIL

    NULL bk@cast.com

    NULL bk@cast.com

    NULL bk@cast.com

    this one is perfectly fine

    but when I run the same query with not like clause to filter out some stuff there no values at all

    even though first name is null and I am filtering out on not like '%800%'

    select firstname,email

    from gsiftp.dbo.wrk_prospects where email like '%BK@Filson%'

    and FIRSTNAME not like '%800%'

    in this situation the resultset is empty

    First Name Email

    why would it filter out bk@cast.com even though the first name is not like '%800%'

    Thanks for Anticipated co-operation!!

  • October 23, 2008 at 12:24 pm

    #888792

    NULL does not work the way that you are thinking.

    Try this command:

    SELECT CASE When NULL=1 Then 'one' When Not NULL=1 Then 'Not' Else 'Else' End

    , CASE When NULL Like '%X%' Then 'one' When Not NULL Like '%X%' Then 'Not' Else 'Else' End

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 23, 2008 at 12:33 pm

    #888798

    NULL is not like anything and NULL is not not like anything. A comparison with null (other than IS NULL or IS NOT NULL) will never return true.

    http://sqlinthewild.co.za/index.php/2008/01/16/comparisons-with-null/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 23, 2008 at 12:57 pm

    #888820

    GilaMonster (10/23/2008)

    NULL is not like anything and NULL is not not like anything.

    Heh, OK, say that ten times, fast. :laugh:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply