This was a query from reporting team. Question looked too simple , but the solution was not obvious.
ProblemWe 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.
- 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