Can the "IN" operator use LIKE-wildcards (%) in Oracle?

Question

I have searched this question, and found an answer in MySQL but this is one of those incidents where the statement fails to cross over into Oracle.

Can I use wildcards in "IN" MySQL statement?
pretty much sums up my question and what I would like to do, but in Oracle

I would like to find the legal equivalent of

Select * from myTable m
where m.status not in ('Done%', 'Finished except%', 'In Progress%')

Thanks for any help

score 15 · Accepted Answer · answered Mar 02 '12 at 20:51

15

Select * from myTable m
where m.status not like 'Done%' 
and m.status not like 'Finished except%'
and m.status not like 'In Progress%'

answered Mar 02 '12 at 20:51

CFL_Jeff

2,589
3
31
49

I was trying to avoid the long route, but it looks like it might be the only way – Matt Mar 02 '12 at 20:53
@Matt If this is just about writing slightly shorter SQL text, than just use the syntax proposed in this answer and learn live with it. If this is about LIKEing rows from another table (so you can't really construct the list of values in advance), then this is a different problem altogether, which would require something like: `JOIN OTHER_TABLE ON NOT (YOUR_TABLE.FIELD LIKE OTHER_TABLE.FIELD)`... – Branko Dimitrijevic Mar 02 '12 at 21:13
I ended up using this for simplicity sake, but the regex answers were helpful as an opportunity to learn a new bit of Oracle. Thanks all – Matt Mar 06 '12 at 16:54

score 11 · Answer 2 · answered Mar 02 '12 at 20:54

11

It seems that you can use regexp too

WHERE NOT REGEXP_LIKE(field, '^Done|^Finished')

I'm not sure how well this will perform though ... see here

answered Mar 02 '12 at 20:54

Sebastian Piu

7,838
1
32
50

Justin Pihony · Answer 3 · 2012-03-02T21:01:28.633

This appears to fit what you are looking for: https://forums.oracle.com/forums/thread.jspa?threadID=2140801

Basically, you will need to use regular expressions as there appears to be nothing built into oracle for this.

I pulled out the example from the thread and converted it for your purposes. I suck at regex's, though, so that might need tweaked :)

SELECT  *
FROM myTable m
WHERE NOT regexp_like(m.status,'((Done^|Finished except^|In Progress^)')

score 3 · Answer 4 · answered Mar 02 '12 at 21:27

Not 100% what you were looking for, but kind of an inside-out way of doing it:

SQL> CREATE TABLE mytable (id NUMBER, status VARCHAR2(50));

Table created.

SQL> INSERT INTO mytable VALUES (1,'Finished except pouring water on witch');

1 row created.

SQL> INSERT INTO mytable VALUES (2,'Finished except clicking ruby-slipper heels');

1 row created.

SQL> INSERT INTO mytable VALUES (3,'You shall (not?) pass');

1 row created.

SQL> INSERT INTO mytable VALUES (4,'Done');

1 row created.

SQL> INSERT INTO mytable VALUES (5,'Done with it.');

1 row created.

SQL> INSERT INTO mytable VALUES (6,'In Progress');

1 row created.

SQL> INSERT INTO mytable VALUES (7,'In progress, OK?');

1 row created.

SQL> INSERT INTO mytable VALUES (8,'In Progress Check Back In Three Days'' Time');

1 row created.

SQL> SELECT *
  2  FROM   mytable m
  3  WHERE  +1 NOT IN (INSTR(m.status,'Done')
  4            ,       INSTR(m.status,'Finished except')
  5            ,       INSTR(m.status,'In Progress'));

        ID STATUS
---------- --------------------------------------------------
         3 You shall (not?) pass
         7 In progress, OK?

SQL>

score 1 · Answer 5 · answered Jan 29 '15 at 22:08

Somewhat convoluted, but:

Select * from myTable m
join (SELECT a.COLUMN_VALUE || b.COLUMN_VALUE status
FROM   (TABLE(Sys.Dbms_Debug_Vc2coll('Done', 'Finished except', 'In Progress'))) a
JOIN (Select '%' COLUMN_VALUE from dual) b on 1=1) params
on params.status like m.status;

This was a solution for a very unique problem, but it might help someone. Essentially there is no "in like" statement and there was no way to get an index for the first variable_n characters of the column, so I made this to make a fast dynamic "in like" for use in SSRS.

The list content ('Done', 'Finished except', 'In Progress') can be variable.

This is very valuable answer since it scales well for multiple patterns. My use case was to search inside string, so I rolled similar but little bit simplified solution in my own answer. — Tomáš Záluský, Jun 23 '17 at 16:17

score 0 · Answer 6 · answered Jun 23 '17 at 16:14

The closest legal equivalent to illegal syntax mentioned in question is:

select * from myTable m
where not exists (
  select 1
  from table(sys.ku$_vcnt('Done', 'Finished except', 'In Progress')) patterns
  where m.status like patterns.column_value || '%'
)

Both mine and @Sethionic's answer make possible to list patterns dynamically (just by choosing other source than auxiliar sys.whatever table).

Note, if we had to search pattern inside string (rather than from the beginning) and database contained for example status = 'Done In Progress', then my solution (modified to like '%' || patterns.column_value || '%') would still generate one row for given record, whileas the @Sethionic's solution (modified to another auxiliar join before a) would produce multiple rows for each pattern occurence. Not judging which is better, just be aware of differences and choose which better fits your need.

Can the "IN" operator use LIKE-wildcards (%) in Oracle?

6 Answers6

Linked

Related