Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

Oracle - BLOB Extract Line

I have a table with a BLOB field type.  This field has multiple lines, but I only one to retrieve the line with a specific keyword.

So my data can be like this:

TEST MATERIAL
COMMENT:  Approved 3/25/207


With my query, I only want to pull the line with keyword "COMMENT:", and parse anything after the colon.

So my output would be the following:

Approved 3/25/2007

Is this possible with a BLOB field type?  See below for the query which I can conver the BLOB into varchar2.  However, I need to only parse the lines with "COMMENT:" and only anything after the colon for that line.

SELECT N.PART_ID, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(N.NOTE)) AS NOTES
FROM   NOTATIONS N
ASKER CERTIFIED SOLUTION
Avatar of Russ Suter
Russ Suter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You shouldn't need to convert to character.  The DBMS_LOB.SUBSTR function (documented here) claims that it can take a BLOB as a parameter.  In order to find the portion of the LOB that you needed, you would also need to look at DBMS_LOB.INSTR (documented here), which should also take a BLOB as a parameter.