Microsoft KB Archive/911848

= SQL Server incorrectly returns rows that have a NULL value when you try to filter non-Unicode data columns for records from a table =

Article ID: 911848

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 474683 (SQL Server 8.0)



SYMPTOMS
Consider the following scenario. You try to filter non-Unicode data columns for records from a table in Microsoft SQL Server 2000. You use the NOT LIKE clause in a Transact-SQL query statement. You use the &quot;%&quot; wildcard character in the search pattern. In this scenario, SQL Server incorrectly returns rows that have a NULL value for the column.

Note You do not experience this problem when you use Unicode data.



WORKAROUND
To work around this problem, you must explicitly specify the IS NOT NULL clause in the Transact-SQL query statement. For example, you may use the following query: select * from test where C2 not like '%' and C2 IS NOT NULL



Steps to reproduce the problem
  In SQL Server 2000 Query Analyzer, paste the following code example. create table test( id int identity(1,1) not null, C2 varchar(50)); go insert into test (C2) values(null); insert into test (C2) values('12345'); insert into test (C2) values('67890'); insert into test (C2) values(''); go   On the Query menu, click Run.

Notice that you receive different results when you run the following queries:

Query 1 select * from test where C2 not like '%' This query returns the values that are listed in the following table.

Query 2 select * from test where c2 not like N'%' This query returns no rows. 

Keywords: kbtshoot kbprb KB911848

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.