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(D BMS_LOB.SU BSTR(N.NOT E)) AS NOTES
FROM NOTATIONS N
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(D
FROM NOTATIONS N
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.