Split Column Values into Multiple Rows

Note:

One should have a delimiter in order to split the column into Multiple Rows. I have used “chr(10)” in my case. Please note you use any kind of delimiter and use the below defined query by replacing "chr(10)" 

    SELECT CASE

              WHEN LEVEL = 1

              THEN

                 SUBSTR (col, LEVEL, REGEXP_INSTR (col,

                                                   CHR (10),

                                                   1,

                                                   LEVEL))

              WHEN LEVEL > 1

              THEN

                 SUBSTR (col, REGEXP_INSTR (col,

                                            CHR (10),

                                            1,

                                            LEVEL - 1)

                              + 1, REGEXP_INSTR (col,

                                                 CHR (10),

                                                 1,

                                                 (LEVEL - 1) + 1)

                                   - REGEXP_INSTR (col,

                                                   CHR (10),

                                                   1,

                                                   (LEVEL - 1)))

           END

              col

      FROM (SELECT    'BIJOY'

                   || CHR (10)

                   || 'MANISH'

                   || CHR (10)

                   || 'ABHAY'

                   || CHR (10)

                   || 'SUBLAL'

                   || CHR (10)

                   || 'PANKAJ'

                   || CHR (10)

                      col

              FROM DUAL)

CONNECT BY LEVEL <= REGEXP_COUNT (col, CHR (10))

Without Using CASE Statement

    SELECT SUBSTR (col, DECODE (LEVEL,

                                1, LEVEL,

                                REGEXP_INSTR (col,

                                              CHR (10),

                                              1,

                                              LEVEL - 1)

                                + 1), DECODE (LEVEL,

                                              1, REGEXP_INSTR (col,

                                                               CHR (10),

                                                               1,

                                                               LEVEL),

                                              REGEXP_INSTR (col,

                                                            CHR (10),

                                                            1,

                                                            (LEVEL - 1) + 1)

                                              - REGEXP_INSTR (col,

                                                              CHR (10),

                                                              1,

                                                              (LEVEL - 1)))) col

      FROM (SELECT    'BIJOY'

                   || CHR (10)

                   || 'MANISH'

                   || CHR (10)

                   || 'ABHAY'

                   || CHR (10)

                   || 'SUBLAL'

                   || CHR (10)

                   || 'PANKAJ'

                   || CHR (10)

                      col

              FROM DUAL)

CONNECT BY LEVEL <= REGEXP_COUNT (col, CHR (10))

Example using “/” as delimiter

   SELECT SUBSTR (col, DECODE (LEVEL,

                                1, LEVEL,

                                REGEXP_INSTR (col,

                                              '/',

                                              1,

                                              LEVEL - 1)

                                + 1), DECODE (LEVEL,

                                              1, REGEXP_INSTR (col,

                                                               '/',

                                                               1,

                                                               LEVEL),

                                              REGEXP_INSTR (col,

                                                            '/',

                                                            1,

                                                            (LEVEL - 1) + 1)

                                              - REGEXP_INSTR (col,

                                                              '/',

                                                              1,

                                                              (LEVEL - 1)))

                                      - 1)

              col

      FROM (SELECT 'BIJOY/MANISH/ABHAY/SUBLAL/PANKAJ/' col FROM DUAL)

CONNECT BY LEVEL <= REGEXP_COUNT (col, '/')