MySQL: NOT LIKE

Question

I have these text in my db,

categories_posts
categories_news
posts_add
news_add

And I don't want to select the rows with categories, I use a query something like this,

SELECT *
    FROM developer_configurations_cms

    WHERE developer_configurations_cms.cat_id = '1'
    AND developer_configurations_cms.cfg_variables LIKE '%parent_id=2%'
    AND developer_configurations_cms.cfg_name_unique NOT LIKE '%categories%'

but it returns these two in the output as well...

categories_posts
categories_news

How can I ignore them in my query?

Thanks.

Am I understand You right? "categories_posts" and "categories_news" are values of "developer_configurations_cms.cfg_name_unique" column? — Piotr Salaciak, Mar 18 '11 at 00:45
in would be enough to say "NOT LIKE 'categories%'", with that values. But this won't solve your problem — Dalen, Mar 18 '11 at 00:46
AND developer_configurations_cms.cfg_name_unique LIKE '%add' — Lawrence Cherone, Mar 18 '11 at 00:48
@Dalen: "NOT LIKE 'categories%'" - works fine for me. but why won't it solve my problem? — Run, Mar 18 '11 at 00:53
i guess with that values %categories% and categories% does the same thing. did you query return the correct resultset now? — Dalen, Mar 18 '11 at 00:56

score 90 · Accepted Answer · answered Mar 18 '11 at 08:51

categories_posts and categories_news start with substring 'categories_' then it is enough to check that developer_configurations_cms.cfg_name_unique starts with 'categories' instead of check if it contains the given substring. Translating all that into a query:

SELECT *
    FROM developer_configurations_cms

    WHERE developer_configurations_cms.cat_id = '1'
    AND developer_configurations_cms.cfg_variables LIKE '%parent_id=2%'
    AND developer_configurations_cms.cfg_name_unique NOT LIKE 'categories%'

Isn't `%` zero or more characters? At least in Oracle that's the case. The OP's original query should work based on what was intended. — dokgu, May 30 '18 at 18:30

score 8 · Answer 2 · answered Mar 18 '11 at 07:44

8

I don't know why

cfg_name_unique NOT LIKE '%categories%'

still returns those two values, but maybe exclude them explicit:

SELECT *
    FROM developer_configurations_cms

    WHERE developer_configurations_cms.cat_id = '1'
    AND developer_configurations_cms.cfg_variables LIKE '%parent_id=2%'
    AND developer_configurations_cms.cfg_name_unique NOT LIKE '%categories%'
    AND developer_configurations_cms.cfg_name_unique NOT IN ('categories_posts', 'categories_news')

answered Mar 18 '11 at 07:44

Piotr Salaciak

1,653
1
15
28

thanks. cfg_name_unique NOT LIKE 'categories%' - will just work! lol – Run Mar 19 '11 at 04:15
1

the 2 lines are redundant! only one is needed. – JDuarteDJ Feb 27 '14 at 11:51
1

What is theres another name that needs to be ignored like `categories_articles` you have to go back and add that to the query. The accepted answer is much better for future proofing – locrizak Nov 17 '16 at 15:32

MySQL: NOT LIKE

2 Answers2

Linked