SQL “IN”运算符可以在 Oracle 中使用 LIKE 通配符 (%) 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9540087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can the "IN" operator use LIKE-wildcards (%) in Oracle?
提问by Matt
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
谢谢你的帮助
采纳答案by CFL_Jeff
Select * from myTable m
where m.status not like 'Done%'
and m.status not like 'Finished except%'
and m.status not like 'In Progress%'
回答by Sebastian Piu
回答by Justin Pihony
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^)')
回答by Tebbe
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>
回答by Sethionic
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 字符的索引，所以我这样做是为了在 SSRS 中使用快速动态“in like”。
The list content ('Done', 'Finished except', 'In Progress') can be variable.
列表内容（“完成”、“完成除外”、“进行中”）可以是可变的。
回答by Tomá? Zálusky
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.whatevertable).
我的和@Sethionic 的答案都可以动态列出模式（只需选择辅助sys.whatever表以外的其他来源）。
Note, if we had to search pattern insidestring (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'，那么我的解决方案（修改为like '%' || patterns.column_value || '%'）仍将为给定记录生成一行，而@Sethionic 的解决方案（修改为另一个辅助连接） before a) 将为每个模式出现生成多行。不要判断哪个更好，只需注意差异并选择更适合您的需求。
