Not Like Operator Weird Result Count

Question

I have a table with 31,483 records in it. I would like to search this table using both a LIKE and a NOT LIKE operator. The user can select the Contains or the Does Not Contain option.

When I do a LIKE the where clause is as follows

WHERE Model LIKE '%test%'

Which filters the result set down to 1345 records - so all is fine and Dandy

HOWEVER

I expected that running a not like on the table would result in n records where n = totalRowCount - LikeResultSet which results in an expected record count of 30138 when running a NOT LIKE operation.

I ran this WHERE clause:

WHERE Model NOT LIKE '%test%'

However it returned 30526 records.

I am assuming there is some intricacy of the NOT LIKE operator I am not realising.

so my question

Why arent I recieving a record count of TotalRows - LikeResults?

I am using SQL Server Compact 4.0 C# Visual Studio 2012

Answer 1

Check if some Model values are nulls, e.g. for the simple artifitial table

  with data as (
    select 'test' as model
     union all
    select 'abc'
     union all
    select 'def'
     union all
    select null -- <- That's vital
 )

you'll get

  -- 4 items
  select count(1)
    from data 

  -- 1 item: 'test'
  select count(1)
    from data 
   where model like '%test%'

  -- 2 items: 'abc' and 'def'
  select count(1)
    from data 
   where model not like '%test%'

And so 1 + 2 != 4

Answer 2

There is NULL value in your data and LIKE will ignore NULL value.

You can test this and see the behavior.

create table a
(
    nama varchar(30)
)

insert into a values ('iswanto');
insert into a values (null);
insert into a values ('joko');
insert into a values ('tukul');
insert into a values ('iswanto2');
insert into a values (null);

select * from a where nama  like '%w%' // 2 record
select * from a where nama not like '%w%' // 2 record

If you want include NULL value in your query, try use COALESCE function.

select * from a where coalesce(nama, '')  like '%w%' // 2 record
select * from a where coalesce(nama, '')  not like '%w%' // 4 record

Answer 3

Your WHERE clause should be changed to WHERE ISNULL(Model,'') LIKE '%test%' to replace NULLs with empty strings.

Answer 4

because like and not like ignores null field value to set try isnull() function in query.

  WHERE Model NOT LIKE '%test%' || isnull(Model)

it will return 30138 records