Oracle:在文本字段中使用 IN 子句? [英] Oracle: using IN clause with text field?

问题描述

可能的重复:
如何在 oracle 9i 中最好地拆分 csv 字符串

我有一些遗留数据，其中有一个 VARCHAR2(100) 字段 SUBID 具有逗号分隔的数据:

I have some legacy data where there's a VARCHAR2(100) field SUBID that has comma-delimited data:

empno   subid
1       1, 3, 2
2       18,19, 3, 6, 9

等

我需要编写相当于

select * 
  from table 
 where id in ( select SUBID from subidtable where empno = 1 )

有没有办法在 Oracle 中实现这一点?

Is there a way to accomplish this in Oracle?

添加了一些说明.我需要对存储在字符串中的单行而不是所有行的值执行 IN 子句.

Added some clarification. I need to do the IN clause against the values stored in a string from a single row, not all rows.

推荐答案

可以，但是有点难看.取决于 Oracle 版本

You can, but it's a little ugly. Depending on the Oracle version

您可以使用 这个 askTom 线程 将数据解析成一个集合并在您的 SQL 语句中使用该集合.这应该适用于自 8.1.5 以来的任何版本的 Oracle，但这些年来语法变得更简单了.

You can use a variant of this askTom thread to parse the data into a collection and use the collection in your SQL statement. This should work in any version of Oracle since 8.1.5 but the syntax has gotten a bit simpler over the years.

SQL> create or replace type myTableType as table
  2       of varchar2 (255);
  3  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace
  2       function in_list( p_string in varchar2 ) return myTableType
  3    as
  4        l_string        long default p_string || ',';
  5        l_data          myTableType := myTableType();
  6        n               number;
  7    begin
  8      loop
  9          exit when l_string is null;
 10          n := instr( l_string, ',' );
 11          l_data.extend;
 12          l_data(l_data.count) :=
 13                ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 14          l_string := substr( l_string, n+1 );
 15     end loop;
 16     return l_data;
 17*  end;
SQL> /

Function created.

SQL> select ename
  2    from emp
  3   where empno in (select column_value
  4                     from table( in_list( '7934, 7698, 7521' )));

ENAME
----------
WARD
BLAKE
MILLER

您还可以使用 此 StackOverflow 中讨论的正则表达式线程

SQL> ed
Wrote file afiedt.buf

  1  select ename
  2    from emp
  3   where empno in (select regexp_substr(str, '[^,]+',1,level)
  4                     from (select '7934, 7698, 7521' str from dual)
  5*                 connect by level <= regexp_count(str,'[^,]+'))
SQL> /

ENAME
----------
WARD
MILLER
BLAKE

这篇关于Oracle:在文本字段中使用 IN 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！