The following regular expression separates the comma separated plain values (SELECT regexp_substr(:pCsv,'[^,]+', 1, level) FROM DUAL CONNECT BY regexp_substr(:pCsv, '[^,]+', 1, level) IS NOT NULL); Exampple: 300100033146068, 300100033146071 , 300100033146079 returns 300100033146068 300100033146071 300100033146079
this works fine if we use the regular expression with SQL IN operator select * from mytable where t.mycolumn IN (SELECT regexp_substr(:pCsv,'[^,]+', 1, level) FROM DUAL CONNECT BY regexp_substr(:pCsv, '[^,]+', 1, level) IS NOT NULL);
But this query is not working if the comma separated value is a single quoted string literal 'one' , ' two' , 'three'