Wednesday, October 21, 2015

String list to table row

String list to table row
Today I have small question about PL/SQL string list to table row.following simple example will help for solution. 

WITH DATABASEUSERGROUP AS
     ( SELECT 'DATABASEUSERGROUP01,DATABASEUSERGROUP002,
DATABASEUSERGROUP0003,,N/A,
DATABASEUSERGROUP00004,DATABASEUSERGROUP000005'
strList FROM dual
      )
    SELECT trim(REGEXP_SUBSTR(strList, '[^,]+', 1, LEVEL)) strList
    FROM DATABASEUSERGROUP
    CONNECT BY INSTR(strList, ',', 1, LEVEL - 1) > 0;

No comments:

Post a Comment