Monday, 7 November 2016

How to use regexp_substr in oracle sql

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

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)
FROM product_tab
    CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (product, '[^,]+'))  + 1
       ORDER BY 1;

No comments:

Post a Comment