Split comma separated string and pass to IN clause of SQL SELECT statement

This was a query from reporting team. Question looked too simple , but the solution was not obvious. 

Problem

We get a colon separated string (1:3)  that we would need to pass to the IN clause of a select statement to get the required rows. 
SQL>select * from test;ID Text1   A2   B3   CSQL>select * from test where ID in :P_INPUT  #P_INPUT=1:3No rows selected
Here we expected 2 rows instead we got no rows. 

Solution

• Split the input string based on delimiter

• Get the individual strings as rows

For the above, we use a combination of  regexp_substr function and connect by features in oracle. 

SQL>select * from test where ID in (     select regexp_substr(:P_INPUT,'[^:]+', 1, level) from dual  connect by regexp_substr(:P_INPUT, '[^:]+', 1, level) is not null;)ID Text1   A3   C