Monday, April 18, 2016

Conver String Split Comma separated Columns value into row


I have a table that save comma separated values in column

col_Name
aa,bb,cc
dd
 
I need output like this
 aa
 bb
 cc
 dd

To achieve this I create below view :

CREATE OR REPLACE FORCE VIEW "VU_COMMA_STRING_TO_ROW" ("col_Name") AS
  select "col_Name" from (WITH     cntr     AS
(
     SELECT     LEVEL     AS n
     FROM     dual
     CONNECT BY  LEVEL <= 1 + ( SELECT  MAX ( LENGTH ( REGEXP_REPLACE ( col_Name
                                                                             , '[^,]'
                                 )     )                  )
                       FROM    table_Name
                     )
)
SELECT         REGEXP_SUBSTR ( table_Name.col_Name
                , '[^,]+'          -- Use + here, not *
                , 1
                , cntr.n
                ) AS FINENUMBER
FROM     table_Name
JOIN     cntr     ON     cntr.n     <= LENGTH ( REGEXP_REPLACE (col_Name|| ',', '[^,]'))

);

Hope It will be useful for someone.

No comments:

Post a Comment