Oracle Converting delimited string to column

WITH
test AS
( select
column_value AS c1
FROM
TABLE( SYS.DBMS_DEBUG_VC2COLL( 'CAT','DOG','MOUSE' , 'RAT' , 'BAT' , 'CAR' , 'DOOR' , 'LOCK') )
)
SELECT * FROM test ;



Output will like following

C1
CAT
DOG
MOUSE
RAT
BAT
CAR
DOOR
LOCK

Oracle getting nth string from comma separated string

For example getting 2nd string from 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK'  will be DOG

SELECT REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 2) AS VAL
FROM
(
   SELECT 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS MYCOL FROM DUAL
);

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.

 JAVA COLLECTION

The Java Collections API's provide Java developers with a set of classes and interfaces that makes it easier to handle collections of objects. In a sense Collection's works a bit like arrays, except their size can change dynamically, and they have more advanced behaviour than arrays.

Most of the Java collections are located in the java.util package. Java also has a set of concurrent collections in the java.util.concurrent package

Java Collections
Java Collections Introduction Overview of Interfaces     Iterable    Collection     Generic Collections         List    Set SortedSet    NavigableSet    Map    SortedMap NavigableMap     Queue     Deque    Stack         hashCode() and equals()     Sorting