add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, October 14

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;

No comments: