How to use regexp_substr

regexp_substr cabe used to transpose the column values as rows

In the below example PRODUCT_TAB is a table ename and product are columns

PRODUCT_TAB

===========

ename        product

B               1,2,3

Product column has data like that only separated by commas

Output should be like this.

Ename     Product

A             1

A             2

A             3

B             1

B             2

B             3

 

If you want the above desired output please use the below solution

SELECT DISTINCT EMP,regexp_substr (product, '[^,]+', 1, level)
product
FROM product_tab
    CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (product, '[^,]+'))  + 1
       ORDER BY 1;