“IN”运算符可以在Oracle中使用LIKE通配符（％）吗？

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.

我已经搜索了这个问题，并在MySQL中找到了答案，但这是该声明无法跨越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

我可以在“IN”MySQL语句中使用通配符吗？几乎总结了我的问题以及我想做的事情，但在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

谢谢你的帮助

6 个解决方案

#1

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

#2

It seems that you can use regexp too

看来你也可以使用正则表达式

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

在哪里没有REGEXP_LIKE（字段，'^完成| ^已完成'）

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

我不确定这会有多好......但请看这里

#3

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

这似乎符合您的要求：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.

基本上，您将需要使用正则表达式，因为似乎没有内置到oracle中。

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

#4

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

不是100％你正在寻找的东西，而是一种从内到外的方式：

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>

#5

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.

这是一个非常独特的问题的解决方案，但它可能会帮助某人。基本上没有“in like”语句，并且没有办法获得列的第一个variable_n字符的索引，所以我这样做了一个快速动态“in like”用于SSRS。

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

列表内容（'完成'，'完成除'，'进行中'）可以是变量。

#6

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).

我和@ Sethionic的答案都可以动态列出模式（只需选择其他来源，而不是辅助的sys.whatever表）。

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.

注意，如果我们必须在字符串内搜索模式（而不是从头开始）和包含的数据库，例如status ='Done In Progress'，那么我的解决方案（修改为'％'|| patterns.column_value ||'％' ）仍然会为给定记录生成一行，而@ Sethionic的解决方案（在a之前修改为另一个辅助连接）将为每个模式出现产生多行。不判断哪个更好，只要注意差异并选择哪个更适合您的需求。

#1

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

#2