Displaying single column data into multiple rows in sql

Displaying single column data into multiple rows in sql

For example employee table has a column enames contains data as

SQL> SELECT ENAMES FROM EMPLOYEE;

ENAMES
-----------------------
CLARK,KING,MILLER


then we want to split that data into multiple rows(based on delimeter(, or . or |)) as follows

SQL>  select regexp_substr(ENAMES,'[^,]+', 1, level) result from EMPLOYEE
  2     connect by regexp_substr(ENAMES, '[^,]+', 1, level) is not null;

result
--------------------------------------------------------------------------------
CLARK
KING
MILLER