Oracle Comma to Row conversion

SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value
FROM   (SELECT ',' || 'first,second,third,fourth,fifth,sixth' || ',' str FROM dual)
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1;


Just replace your comma separated string 'first,second,third,fourth,fifth,sixth'  with your comma separated words.


You can also use the same query for retrieving the output , put the same query in the inner query and fetch the output

SELECT * FROM  mstlocations WHERE mstloc_pincode IN
 (
SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value
FROM   (SELECT ',' || '411038,411028' || ',' str FROM dual)
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1
);

The above query can be written by using LIKE operator but the performance will be little bit slow if records are huge.