Oracle SQL: Return string from blob between two strings (probably using REGEXP_SUBSTR)

Question

I am trying to export data from a blob to use in a report, however I am having trouble using REGEXP_SUBSTR when trying to read from it. Converting it to a varchar is helping, however getting the data point I need is quite hard as I am finding!

Here is how I am getting it to a varchar:

select utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE)) FROM prod17import;

This is a sample of the output to varchar:

"Import: T5, ModId: #24, Time: 1/11/2017 7:32:30 AM
Records read: 1723
Added:  1723, Changed: 0
Rejected: 0, Skipped: 0
0.01 Minutes. 234422 recs/min
Map=IMCOWITHHOLDINGLAYOUTTEST9A.MPWM 
Source=W:\(filename).TXT, created: 1/10/2017 11:15:44 PM

Reject name: REJECT
Resource name: !6Imports.Liability.Maps.Life Import Resource
"

I am trying to gather only the value 234422 (which can vary between 1-1,000,000)

How do I get that value between the word "Minutes." and "recs/min"?

Why BLOB and not CLOB? BLOB is for binary files, not for text. You shouldn't expect it to be easy to extract text from BLOB. — , Aug 27 '17 at 15:47

score 0 · Accepted Answer · answered Aug 27 '17 at 14:18

If the string format is consistent, use

select regexp_substr(utl_raw.cast_to_varchar2(dbms_lob.substr(NOTE)) 
                     ,'minutes. ([0-9]+) recs/min',1,1,'i',1)
from prod17import

i as the argument specifies case-insensitive matching. Use null if you need the match to be case-sensitive.

Oracle SQL: Return string from blob between two strings (probably using REGEXP_SUBSTR)

1 Answers1