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

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 "%" 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

MORE INFORMATION

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.
id C2
2 NULL
Query 2
```
select * from test where c2 not like N'%'
```
This query returns no rows.