is it possible to use % operator in the NOT IN() to look for word parts instead of exact phrases?

Question

Using MySQL, is there a way to use something similar to the "LIKE '%closed%' but used in the "NOT IN()" function?

I have data in a table that allows the end user to specify their own "closed" status for a work order. Some of the users write their own status as follows:

Closed
Pending Approval
1-Open
3-Closed
2-In progress
Pending

As you can see, I have a status called "3-Closed". I am trying to gather all the records that are NOT considered "closed", but this "3-Closed" will not work with my "NOT IN()" sql below.

SELECT * FROM my_table WHERE
    p_wo_status_cat_id IN 
             (
              SELECT b1.p_wo_status_cat_id 
              FROM p_status_list b1 
              WHERE LOWER(b1.name) NOT IN('completed','finished','done','closed')

              )

Is there a way to do some thing like this:

NOT IN ('%closed%')

so it would pick up any variances of the word "closed"?

Thanks in advance.

possible duplicate of [Using SQL LIKE and IN together](http://stackoverflow.com/questions/2318126/using-sql-like-and-in-together) — Ben, Sep 19 '13 at 21:14
Not an very good idea to use an WHERE ... IN ( SELECT .... syntax this will force an full table scan on the table my_table.. if p_wo_status_cat_id is an primary key or unique key you should really rewrite this to an INNER JOIN this will than run better on large tables — Raymond Nijland, Sep 19 '13 at 21:19
What are `completed`, `finished` and `done`? Are they user statuses? If you are listing them explicitly, why don't you want to list the `3-Closed` status explicitly as well? — Andriy M, Sep 19 '13 at 21:20
good point Raymond...i've rewritten it to join on the table on the primary key you suggested. Thanks. — Ronedog, Sep 19 '13 at 21:30
I dont know if this would help [SQLFIDDLE](http://sqlfiddle.com/#!2/c809ff/2/0) — furier, Sep 19 '13 at 21:36

score 4 · Accepted Answer · answered Sep 19 '13 at 21:15

4

Not with the same syntax as IN, but REGEXP can do this. For a single comparison:

NOT REGEXP 'closed'

For multiple comparisons (i.e. match anything that does not contain either "foo" or "bar":

NOT REGEXP '(foo|bar)'

answered Sep 19 '13 at 21:15

Jon

428,835
81
738
806

Perfect! This worked like a charm and accomplished exactly what I needed it to! Thanks! – Ronedog Sep 19 '13 at 21:25

score 0 · Answer 2 · answered Sep 19 '13 at 21:19

There is no way to do that. The IN funciton takes exact matches.

You can use NOT LIKE.

A better way to write the query would potentially be to restructure your data. Add a column that says is_closed or is_closing_statment that is a bit. Then a 1 means that this word closes the issue and you can rewrite your query to only get the staments that close the issues. Using your sample data:

Closed                 1
Pending Approval       0
1-Open                 0
3-Closed               1
2-In progress          0
Pending                0

Then you filter to states that close and limit your results to issues that have that state.

is it possible to use % operator in the NOT IN() to look for word parts instead of exact phrases?

2 Answers2