在 Oracle SQL 中的 replace() 函数的输出中使用带有逗号分隔字符串的“IN”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4672545/
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
Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL
提问by Thomas
I have an comma delimited string which I want to use in an "IN" clause of the statement. eg: 100,101,102
我有一个逗号分隔的字符串，我想在语句的“IN”子句中使用它。例如：100,101,102
Since In and "IN" clause I have to quote the individial strings, I use a replace function: eg: select ''''||replace('100,101,102',',',''', ''')||'''' from dual;
由于 In 和 "IN" 子句我必须引用单个字符串，所以我使用替换函数：例如：select ''''||replace('100,101,102',',',''', ''')|| ''''来自双;
The above query works, however, when I try to use the output of the above as an input to the "IN" clause, it returns no data. I am restricted by only SQL statements, so I cannot use PL/SQL code. Kindly help.
上面的查询有效，但是，当我尝试使用上面的输出作为“IN”子句的输入时，它不返回任何数据。我只受 SQL 语句的限制，所以我不能使用 PL/SQL 代码。请帮忙。
select * from employee where employee_number in (
select ''''||replace('100,101,102',',',''', ''')||'''' from dual);
The above does not work. Please let me know what I am missing.
以上是行不通的。请让我知道我缺少什么。
回答by Justin Cave
The general approach in this case would be to parse the comma-separated list into an Oracle collection and to use that collection in your SQL statement. Tom Kyte has an example of this in his discussion on variable IN lists.
这种情况下的一般方法是将逗号分隔的列表解析为 Oracle 集合并在您的 SQL 语句中使用该集合。Tom Kyte 在他关于变量 IN 列表的讨论中有一个这样的例子。
Assuming you create the myTableType type and the in_list function from that thread, you should be able to do
假设您从该线程创建 myTableType 类型和 in_list 函数，您应该能够做到
SELECT *
FROM employee
WHERE employee_number IN (
SELECT *
FROM TABLE( in_list( p_your_comma_separated_list ) )
)
回答by Markus Winand
pure SQL, but not very well tested...
纯 SQL，但没有经过很好的测试...
select to_number(substr(postfix, 2, instr(postfix, ',' ,2)-2)) id
from (
select substr(val, instr(val, ',', 1, n)) postfix
from (select ',101,102,103,' val from dual)
, (
select level n
from dual
connect by level < 10)
where instr(val, ',', 1, n) > 0)
where instr(postfix, ',' ,2)> 2;
EDIT: improved
编辑：改进
select substr(postfix, 1, instr(postfix, ',' ,1)-1)
from (
select substr(val, instr(val, ',',1, level)+1) postfix
from (select ',101,102,103,' val from dual)
connect by instr(val, ',', 2, level) > 0
);
Note:
笔记：
- pre/post fix your strings with comma
adopt the upper limit (10 in the example) as per your needs(not needed in the improved version).- use the in_list table function mentioned by Justing Cave, that's probably better :)
- 前/后用逗号修复你的字符串
根据您的需要采用上限（示例中为 10）（改进版本中不需要）。- 使用 Justing Cave 提到的 in_list 表函数，这可能会更好:)
credit: something like that is in Stephane Faroult's book "Refactorying SQL Applications" (O'Reilly)
信用：类似的东西在 Stephane Faroult 的书“重构 SQL 应用程序”(O'Reilly) 中
回答by Sylvain Leroux
As the comma-separated values contain only digits, why not try something as simple as using:
由于逗号分隔的值仅包含数字，为什么不尝试使用以下简单的方法：
INSTR(','||my_csv_list_of_values||',', ','||my_search_value||',') <> 0
See this example:
看这个例子：
-- some test data
with employee as (
select 101 as employee_number from dual
union select 200 from dual
union select 10 from dual
union select 102 from dual)
-- the actual query
select * from employee
where INSTR(','||'101,102,103,104'||',', ','||employee_number||',') <> 0;
-- ^^^^^^^^^^^^^^^^^
-- your CSV data
Producing:
生产：
EMPLOYEE_NUMBER
101
102
回答by Jens Krogsboell
You can use your approach with REPLACE and IN if you format the entire select as a string - then use the string with either OPEN refcursor FOR or EXECUTE IMMEDIATE.
如果您将整个选择格式化为字符串，则可以将您的方法与 REPLACE 和 IN 一起使用 - 然后将字符串与 OPEN refcursor FOR 或 EXECUTE IMMEDIATE 一起使用。
回答by Thirumalai
You can use regexp_substr functionto get expected output.
For Example
NAMES:='SMITH,ALLEN,WARD,JONES'; -- here "NAMES" is the variable/result of expected input.that can be use in IN Clause.
您可以使用regexp_substr 函数来获得预期的输出。
例如 NAMES:='SMITH,ALLEN,WARD,JONES'; -- 这里的“NAMES”是预期输入的变量/结果。可以在 IN 子句中使用。
SQL> select regexp_substr(NAMES,'[^,]+', 1, level) from dual 2 connect by regexp_substr(NAMES, '[^,]+', 1, level) is not null;
REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES
The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.
Here is an Reference Click Here
上面的查询遍历逗号分隔的字符串，搜索逗号 (,)，然后通过将逗号视为分隔符来拆分字符串。每当遇到分隔符时，它都会将字符串作为一行返回。
这是参考点击这里
SQL> select * from emp where ename in (
2 select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
3 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
