Replacing Text which does not match a pattern in Oracle
- by kutekrish
I have below text in a CLOB in a table
Table Name: tbl1
Columns
col1 - number (Primary Key)
col2 - clob (as below)
Row#1
-----
Col1 = 1
Col2 =
1331882981,ab123456,Some text here
which can run multiple lines and have a lot of text...
~1331890329,pqr123223,Some more text...
Row#2
-----
Col1 = 2
Col2 =
1331882981,abc333,Some text here
which can run multiple lines and have a lot of text...
~1331890329,pqrs23,Some more text...
Now I need to know how we can get below output
Col1 Value
---- ---------------------
1 1331882981,ab123456
1 1331890329,pqr123223
2 1331882981,abc333
2 1331890329,pqrs23
([0-9]{10},[a-z 0-9]+.), == This is the regular expression to match "1331890329,pqrs23" and I need to know how can replace which are not matching this regex and then split them into multiple rows