Take one string variable and use it in the expression "IN"

Question

I have a procedure that takes a parameter called p_my_list_of_numbers. A comma-separated string that looks like this: '1,4,5,8,9,22,89'.

 PROCEDURE my_procedure ( p_my_list_of_numbers VARCHAR2)
 BEGIN

       SELECT * FROM my_table WHERE ID IN (1,4,5,8,9,22,89); //THIS RETURNS DATA
       SELECT * FROM my_table WHERE ID IN p_my_list_of_numbers; //DOES NOT RETURN ANYTHING

 END;

How can I take this long string and use it in a select query so that it returns data?

Answer 1

You can use the following subquery:

select regexp_substr('1,4,5,8,9,22,89','[^,]+', 1, level) from dual
connect by regexp_substr('1,4,5,8,9,22,89', '[^,]+', 1, level) is not null;

This splits the comma-separated list of values ​​into a result set. Your procedure will look something like this:

 PROCEDURE my_procedure ( p_my_list_of_numbers VARCHAR2)
 BEGIN

       SELECT * FROM my_table 
       WHERE ID IN (
          select regexp_substr(p_my_list_of_numbers,'[^,]+', 1, level) 
          from dual
          connect by regexp_substr(p_my_list_of_numbers, '[^,]+', 1, level) is not null); 

 END;

, , , .