将“IN”子句与Oracle SQL中replace()函数输出的逗号分隔字符串一起使用

jvlzgdj9  于 2023-02-03  发布在  Oracle
关注(0)|答案(6)|浏览(314)

我有一个逗号分隔的字符串,我想在语句的“IN”子句中使用。例如:100,101,102
由于In和“IN”子句必须引用单个字符串,因此使用replace函数:例如:选择“”“||替换(“100、101、102”、“、”、“”)||“”“”来自对偶;
上面的查询可以工作,但是当我尝试使用上面的输出作为“IN”子句的输入时,它没有返回任何数据。我只受SQL语句的限制,所以我不能使用PL/SQL代码。请帮助。

select * from employee where employee_number in (
    select ''''||replace('100,101,102',',',''', ''')||'''' from dual);

以上不起作用,请让我知道我错过了什么。

chy5wohz

chy5wohz1#

在这种情况下,一般的方法是将逗号分隔的列表解析为Oracle集合,并在SQL语句中使用该集合,Tom Kyte在他关于variable IN lists的讨论中有一个这样的示例。
假设您从该线程创建了myTableType类型和in_list函数,则应该能够执行以下操作

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

ubbxdtey2#

纯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;
    • 编辑**:改进
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
  );

注:

  • 前缀/后缀用逗号修正字符串
  • 根据您的需要采用上限(示例中为10)(改进版本中不需要)。
  • 使用Justing Cave提到的in_list表函数,这可能更好:)

功劳:StephaneFaroult的书"重构SQL应用程序"(O'Reilly)中有类似的内容

d8tt03nd

d8tt03nd3#

由于逗号分隔的值只包含数字,为什么不尝试使用这样简单的方法:

INSTR(','||my_csv_list_of_values||',', ','||my_search_value||',') <> 0

请看这个例子:

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

生产:

EMPLOYEE_NUMBER
101
102
v1l68za4

v1l68za44#

如果将整个选择格式化为字符串,则可以使用REPLACE和IN方法,然后将该字符串与OPEN refcursor FOR或EXECUTE IMMEDIATE一起使用。

8hhllhi2

8hhllhi25#

可以使用XMLTABLE将逗号分隔的字符串转换为表。
示例:

select * from employee where employee_number in (
        SELECT  TO_NUMBER (column_value)
        FROM    XMLTABLE('100,101,102') );
juzqafwq

juzqafwq6#

您可以使用regexp_substr函数获得预期输出。
例如姓名:=“史密斯、艾伦、沃德、琼斯”;--此处“名称”是预期输入的变量/结果,可用于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

上面的查询遍历逗号分隔的字符串,搜索逗号(,),然后通过将逗号视为分隔符来拆分字符串。每当遇到分隔符时,它将字符串作为行返回。
这是一个参考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

相关问题