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  quot IN quot  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

User · Answer

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^)')

User · Answer

Select   from myTable m where m status not like  Done    and m status not like  Finished except   and m status not like  In Progress

User · Answer

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

User · Answer

Not 100  what you were looking for  but kind of an inside-out way of doing it   SQL gt  CREATE TABLE mytable  id NUMBER  status VARCHAR2 50     Table created   SQL gt  INSERT INTO mytable VALUES  1  Finished except pouring water on witch     1 row created   SQL gt  INSERT INTO mytable VALUES  2  Finished except clicking ruby-slipper heels     1 row created   SQL gt  INSERT INTO mytable VALUES  3  You shall  not   pass     1 row created   SQL gt  INSERT INTO mytable VALUES  4  Done     1 row created   SQL gt  INSERT INTO mytable VALUES  5  Done with it      1 row created   SQL gt  INSERT INTO mytable VALUES  6  In Progress     1 row created   SQL gt  INSERT INTO mytable VALUES  7  In progress  OK      1 row created   SQL gt  INSERT INTO mytable VALUES  8  In Progress Check Back In Three Days   Time     1 row created   SQL gt  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 gt

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to where

Examples related to wildcard