mysql not like does not work [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 6 months ago.

Improve this question

I have a problem with mysql select using "not like" in where conditions. There are 15k records in table. 3k records are with value 'test' in column col1.

This select works fine:

select 
    *
from
    `table`
where
    `col1` like 'test'

3000 rows selected. This is correct.

But if I try this select:

select
   *
from
   `table`
where
   `col1` not like 'test'

0 rows selected while I expect 12000 rows.

I will be grateful for any ideas?

not sure but try two different querry with like col1 like '%test%' and col1 NOT like '%test%' — Suresh Kamrushi
– Suresh Kamrushi, Commented Jan 23, 2014 at 11:18

sajushko · Accepted Answer · 2014-01-24 00:58:44Z

5

So I solved it. Problem was in data, not in request. I didn't realize that not like condition doesn't work for NULL value.

http://sqlfiddle.com/#!2/17270/2

answered Jan 24, 2014 at 0:58

sajushko

4144 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent Monteil Over a year ago

I build a quick turnaround : select ... where COALESCE(field, "****") not like "test". This will include null values aswell.

Sid M · Accepted Answer · 2014-01-23 11:18:59Z

2

Try this

select * from table where col1 NOT LIKE '%test%';

answered Jan 23, 2014 at 11:18

Sid M

4,3744 gold badges33 silver badges51 bronze badges

1 Comment

Barmar Over a year ago

If he's doing an exact match with LIKE, why shouldn't he do an exact match with NOT LIKE?

Abdul Manaf · Accepted Answer · 2014-01-23 11:25:52Z

2

If you are making exact comparison as you are doing in query, you can use NOT EQUAL

select
   *
from
   `table`
where
   `col1` != 'test'

edited Jan 23, 2014 at 11:25

answered Jan 23, 2014 at 11:16

Abdul Manaf

4,8883 gold badges29 silver badges34 bronze badges

1 Comment

sajushko Over a year ago

Thanks for idea but it didn't work. The same result of 0 rows.

Islan Maicon · Accepted Answer · 2019-11-28 20:17:07Z

1

So I solved my problem with:

where ifnull(column,'') not like 'value'

answered Nov 28, 2019 at 20:17

Islan Maicon

112 bronze badges

1 Comment

David Buck Over a year ago

When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps, particularly for a question that already has an accepted answer. See: How do I write a good answer.

Ashish · Accepted Answer · 2014-01-23 11:19:49Z

0

try this

SHOW TABLES WHERE col1 NOT LIKE '%test%'

answered Jan 23, 2014 at 11:19

Ashish

1,9412 gold badges14 silver badges17 bronze badges

Comments

user2361944 · Accepted Answer · 2014-01-23 11:28:41Z

0

It might be lack of %

select
   *
from
   `table`
where
   `col1` not like '%test%'

Try without `

select
   *
from
   table
where
   col1 not like '%test%'

answered Jan 23, 2014 at 11:28

user2361944

12 bronze badges

2 Comments

Ravinder Reddy Over a year ago

The 2nd solution will not compile.

sajushko Over a year ago

You're request will not use any index a it will explore every single row in table. It will be extremly slow.

Harry · Accepted Answer · 2024-03-20 10:35:53Z

0

I was doing a query similar to:

select col from my_table where not like 'some_text%'

and it returned no results when I expected some.

It turns out the underscore needs escaping

select col from my_table where not like 'some\_text%'

answered Mar 20, 2024 at 10:35

Harry

2,5664 gold badges23 silver badges27 bronze badges

Collectives™ on Stack Overflow

mysql not like does not work [closed]

7 Answers 7

1 Comment

1 Comment

1 Comment

1 Comment

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

1 Comment

1 Comment

Comments

2 Comments

Comments

Related