Oracle: using IN clause with text field?

Question

Possible Duplicate:
How to best split csv strings in oracle 9i

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

etc.

I need to write the equivalent of

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

Is there a way to accomplish this in Oracle?

Edit:

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

Are you saying that you need to find records in `table` where `table.id` is in *any* `subidtable.subid`? Like, with the example data you posted, would you need to find all records in `table` with `id` in `(1,2,3,6,9,18,19)`? — ruakh, Feb 08 '12 at 15:05
Can you normalize this table? I.e. can you create another table and convert the comma-separated values into individual numbers? — Szilard Barany, Feb 08 '12 at 15:07
What is the data type of your `id` field from `table` in your example? You should be able to use the `IN` clause with Oracle as long as the data types match (or else convert it). — ProfessionalAmateur, Feb 08 '12 at 15:07
@ProfessionalAmateur - No, the problem is that SUBID is a comma delimited string of integers. Using `IN` on it's own (as shown in the OPs existing example) will not give the necessary results, as `(SELECT subid FROM subidtable)` is a set of strings consisting of numerics and commas, and *not* a set of integers. — MatBailie, Feb 08 '12 at 15:13
@chris - I was initially assuming you wanted your IN clause to look at a particular row's `SUBID` column (i.e. either 1,3,2 or 18,19,3,6,9). Do you really want to combine all the values in all the rows? — Justin Cave, Feb 08 '12 at 15:26

score 3 · Accepted Answer · edited May 23 '17 at 12:22

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

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

You can also use regular expressions as discussed in this StackOverflow thread

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

I was hoping there was a better way to do it. There's actually a package in the database that contains a function with your exact code, and even references that particular asktom article in a comment. So I guess until we can fix the table design, the package lives. — chris, Feb 08 '12 at 19:38

MatBailie · Answer 2 · 2012-02-08T15:58:57.933

If you can 'fix' your table structure to have a 1:many relationship, such that each row in your subidtable contains only one id, that's your best bet.

If you can't, then you could get hold of one of the many split() functions that people have coded around the web. These take a string and return the data as a set. The problem here is that they are designed to take a single string and return a table of values, not to take a table of strings...

As this data seems to be in a bit of a hacked format, you may only need a one-time hack solution with minimal code. In such cases you can try this...

SELECT
   *
FROM
  table
WHERE
  EXISTS (SELECT * FROM subidtable WHERE (',' || subid || ',') LIKE ('%,' || table.id || ',%'))

But be warned, it scales VERY badly. So expect slow performance if you have a large amount of data in either table.

EDIT

As your edit now shows that you're only ever processing one string from the subidtable table, the split function option becomes a lot easier to implement. See Justin's answer :)

A modification to the 'simple hack' above would be...

SELECT
   *
FROM
  table
WHERE
  (SELECT ',' || subid || ',' FROM subidtable WHERE empno=1) LIKE ('%,' || table.id || ',%')

score 0 · Answer 3 · answered Feb 08 '12 at 17:44

This is in no way elegant and an abuse of "execute immediate" but in your specific case this might work:

DECLARE
  i INTEGER;
  subid VARCHAR2(100) := '18,19, 3, 6, 9';
BEGIN

  EXECUTE IMMEDIATE 'select 1  from dual where 6 in (' || subid || ')'
    INTO i;

  dbms_output.put_line('returned: ' || i);
EXCEPTION
  WHEN others THEN
    dbms_output.put_line('Exception: ' || SQLERRM);
END;

Oracle: using IN clause with text field?

3 Answers3

Linked

Related