Search code examples
oracle11gclob

Extract certain lines from CLOB column

I have a CLOB column where there are lines like this:

[2019-01-24 11:57:05]: Serial.no is: 12330

[2019-01-24 11:57:05]: Buyers: 3

[2019-01-24 11:57:06]: Serial.no is: 23340

[2019-01-24 11:57:06]: Buyers: 6

Now I would like a Select on that column and extract only lines where "Serial.no" is, example:

[2019-01-24 11:57:05]: Serial.no is: 12330
[2019-01-24 11:57:06]: Serial.no is: 23340

I'm hoping for some easy solution because I don't quite understand everything I found so far, thanks in advance.

EDIT:

This returns me one row, first one:

select regexp_substr(My_CLOB, 'Serial.no.*',1) as clob_data from MyTable
WHERE id='1163753';

It returns column in CLOB datatype, but that wouldn't be a problem If I could retrieve all rows.

Solution

  • This is a trick to use the connect by clause to increment the regexp index. Seems to work

    WITH test AS (
     SELECT
         '[2019-01-24 11:57:05]: Serial.no is: 12330
[2019-01-24 11:57:05]: Buyers: 3
[2019-01-24 11:57:06]: Serial.no is: 23340
[2019-01-24 11:57:06]: Buyers: 6'
         my_clob
     FROM
         dual
 )
 SELECT
     regexp_substr(my_clob,'Serial.no.*',1,level) AS clob_data
 FROM
     test
 CONNECT BY
     level <= regexp_count(my_clob,'Serial.no.*')