I have a table whose one of the cell contains multiple values separated by Newline character [chr(10)]
Now I want to convert the value within the column to the row level using regular expression substring
Current Data
S_no
| Products |
|---|
| 1 | 2,500 MG Doxytel+Amoxycylin+LOZ 2,250 MG Doxytell+Amoxycylin+LOZ 4,100 MG Doxytel Capl+Amoxycylin+LOZ |
Expected Data
S_no
| Products |
|---|
| 1 | 2,500 MG Doxytel+Amoxycylin+LOZ |
| 1 | 2,250 MG Doxytell+Amoxycylin+LOZ |
| 1 | 4,100 MG Doxytel Capl+Amoxycylin+LOZ |
I tried using select REGEXP_SUBSTR(PRODUCTS,'[^\A]+',1,level] from dual
connect by REGEXP_SUBSTR(PRODUCTS,'[^\A]+',1,level] is not null;
But it is somehow skipping going beyond "+" and returning only "2,500 MG Doxytel" and skipping "+Amoxycylin+LOZ"
Could you guys please help me out in solving this with regexp_substr ?