Oracle SQL to list all occurrences of string in Clob/Blob Field

Question

I have a field in my Oracle table "Table1" with Column "texta" which has all my codes written. I would like to extract all the occurrences which matches the patter "record." or "Record." and list the result. For example:-

Content of texta

CreateRecord = CreateRecord(Record.PS_JOB)
CreateRecord = CreateRecord(Record.PS_NATIONAL_ID)

The expected result is

PS_JOB
PS_NATIONAL_ID

You can filter record using `REGEXP_LIKE` and trim string using `REGEXP_REPLACE` — Digvijay S, Mar 26 '20 at 13:34
Could someone please explain why the question was downvoted, when I got the reply in 10 mins. I would not make that mistake again. — Rohit Prasad, Mar 26 '20 at 13:42
I have not downvoted. But you can check [this](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) thread on asking `sql` questions. — Digvijay S, Mar 26 '20 at 13:44

score 2 · Accepted Answer · answered Mar 26 '20 at 13:37

See if this helps.

SQL> desc test
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------------
 ID                                                             NUMBER
 TEXTA                                                          CLOB

SQL> select * From test;

        ID TEXTA
---------- ------------------------------------------------------------------------
         1 CreateRecord = CreateRecord(Record.PS_JOB)
           CreateRecord = CreateRecord(Record.PS

         2 CreateRecord = CreateRecord(Record.ABC_DEF)
         3 CreateRecord = CreateRecord(Record.LITTLE_FOOT)

SQL> select id,
  2    regexp_substr(texta, 'Record\.\w+', 1, column_value) result
  3  from test cross join
  4       table(cast(multiset(select level from dual
  5                           connect by level <= regexp_count(texta, 'Record\.')
  6                          ) as sys.odcinumberlist));

        ID RESULT
---------- ------------------------------------------------------------------------
         1 Record.PS_JOB
         1 Record.PS_NATIONAL_ID
         2 Record.ABC_DEF
         3 Record.LITTLE_FOOT

SQL>

You're welcome. As of downvoting: I wouldn't know who did it, but yes - it would help if downvotes required explanation (or if that person posted a(n anonymous) comment). — Littlefoot, Mar 26 '20 at 13:53

Oracle SQL to list all occurrences of string in Clob/Blob Field

1 Answers1