使用“IN"Oracle SQL 中 replace() 函数输出中带有逗号分隔字符串的子句

2021-12-05 00:00:00 oracle

我有一个逗号分隔的字符串，我想在语句的IN"子句中使用它.例如:100,101,102

I have an comma delimited string which I want to use in an "IN" clause of the statement. eg: 100,101,102

由于 In 和 "IN" 子句我必须引用单个字符串，所以我使用了替换函数:例如:select ''''||replace('100,101,102',',',''', ''')||'''' from dual;

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"子句的输入时，它不返回任何数据.我只受 SQL 语句的限制，所以我不能使用 PL/SQL 代码.请帮忙.

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.

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.

推荐答案

这种情况下的一般方法是将逗号分隔的列表解析为 Oracle 集合，并在 SQL 语句中使用该集合.Tom Kyte 在他关于 变量 IN 列表.

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.

假设您从该线程创建 myTableType 类型和 in_list 函数，您应该能够做到

Assuming you create the myTableType type and the in_list function from that thread, you should be able to do

SELECT *
  FROM employee
 WHERE employee_number IN (
    SELECT *
      FROM TABLE( in_list( p_your_comma_separated_list ) )
    )

相关文章