PL SQL create array of number from string separated by comma

Question

I have a string of numbers separated by comma like '7845,6986,2548' I want to split comma and pass result to IN clause of select statement I used to use this function:

create or replace TYPE t_in_list_tab as table of varchar2(4000);

create or replace FUNCTION in_list (p_in_list  IN  VARCHAR2) 
 RETURN t_in_list_tab
AS
  l_tab   t_in_list_tab := t_in_list_tab();
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;

the return type of this function is varchar2 but my column type is number, I tried to use TO_NUMBER like:

select * from my_tbl where col1 IN (TO_NUMBER(select * from table(IN_LIST('7845,6986,2548'))));

but it didn't work, any help?

Same as the duplicate question - you do not need to use `IN ( ( SELECT ... FROM TABLE( ... ) ) )` and can just use `MEMBER OF IN_LIST( '1,2,3' )`. — MT0, Jun 17 '16 at 09:57
You can also have `CREATE TYPE intlist IS TABLE OF INTEGER;` and use that collection and `TO_NUMBER()` inside the function. — MT0, Jun 17 '16 at 10:02

Alex Poole · Answer 1 · 2016-06-17T08:57:41.880

0

You need to put the column from the table collection expression (which is called column_value) inside the to_number() call:

select to_number(column_value) from table(IN_LIST('7845,6986,2548')

Demo:

with my_tbl(col1, col2) as (
  select 7845, 'Test 1' from dual
  union all select 2548, 'Test 2' from dual
  union all select 9999, 'Test 3' from dual
)
select * from my_tbl where col1 IN (
  select to_number(column_value) from table(IN_LIST('7845,6986,2548'))
);

      COL1 COL2 
---------- ------
      7845 Test 1
      2548 Test 2

You could also use a join:

select my_tbl.*
from
(
    select * from table(IN_LIST('7845,6986,2548'))
) tmp
join my_tbl
on my_tbl.col1 = to_number(tmp.column_value);

edited Jun 17 '16 at 08:57

answered Jun 17 '16 at 08:49

Alex Poole

183,384
11
179
318

1

You can also use the `MEMBER OF` collection operator. `SELECT * FROM my_tbl WHERE col1 MEMBER OF IN_LIST( '7845,6986,2548' )` – MT0 Jun 17 '16 at 10:03
@MTO - that only works if the function returns a table of numbers (unless you `to_char(col1)`, which is obviously wrong); it doesn't seem to do implicit conversion. Using a numeric collection makes sense of course, if one is available or can be created. – Alex Poole Jun 17 '16 at 10:17

PL SQL create array of number from string separated by comma

1 Answers1