[thelist] (SQL2k) NULL and LIKE

Jason Handby jasonh at corestar.co.uk
Wed Dec 10 11:52:43 CST 2003


Hi Tab,


> I don't get it.
>
> If I do this query:
>
> SELECT Browser FROM xmlErrorLog
> WHERE (TimeStamp BETWEEN '12/10/2003 00:00' AND '12/10/2003 23:59')
> AND (NOT (ClientIP LIKE '10.1.%'))
> AND (NOT (ClientIP LIKE '192.168.%'))
>
> I get 2 rows:
> NULL
> NULL
>
> But if I modify the query thus:
>
> SELECT Browser FROM xmlErrorLog WHERE (TimeStamp BETWEEN '12/10/2003
> 00:00' AND '12/10/2003 23:59')
> AND (NOT (ClientIP LIKE '10.1.%'))
> AND (NOT (ClientIP LIKE '192.168.%'))
> AND (NOT (Browser LIKE '%RedAlert%')) -- this is the only change
>
> I get no rows.
>
> So, NULL is LIKE '%RedAlert%'???  How so?  Is this expected behavior?


If you have ANSI_NULLS set to ON, then comparisons with NULL return UNKNOWN
(rather than TRUE or FALSE). I'm guessing that (Browser LIKE '%RedAlert%')
is returning UNKNOWN. And, because NOT(UNKNOWN) is UNKNOWN, which is not the
same thing as TRUE, your WHERE clause is failing for those records.

You could try

  ...AND (Browser IS NULL OR NOT(Browser LIKE '%RedAlert%'))

instead?




Jason



More information about the thelist mailing list