Extracting just text from a Blob Column [message #671189] |
Thu, 16 August 2018 11:30 |
|
dejah
Messages: 2 Registered: August 2018
|
Junior Member |
|
|
Good Morning,
I am looking for help extracting just the text from a blob column without the formatting info. I can get the text using UTL_RAW.CAST_TO_VARCHAR2 but it comes with the formatting and wee need to insert the text into another table without the extra formatting text that comes out. Any ideas on how to do this? Thanks in advance
|
|
|
|
|
Re: Extracting just text from a Blob Column [message #671198 is a reply to message #671194] |
Thu, 16 August 2018 12:18 |
|
dejah
Messages: 2 Registered: August 2018
|
Junior Member |
|
|
Thanks, it's my first time posting.
I GOT
-------------------------------
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(COLUMN,2000,1))
--------------------------------------------------------------------------------
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft
Sans Serif;}}
\viewkind4\uc1\pard\f0\fs17 *Paste policy criteria and match with chart notes.\p
ar
*TECH MAY APPROVE: If for eye (diabetic retinopathy, macular degeneration, retin
al vein occlusion, mention of intravitreal use). Please refer to the VEGF job ai
d. \par
*TECH MAY APPROVE: If metastatic (or stage IV) colorectal cancer and will be giv
en with 5FU therapy (FOLFOX, FOLFIRI, CAPEOX, Xeloda).
\par
*For lung cancer, please verify if nonsquamous type of non small cell lung cance
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(SOC,32000,1))
--------------------------------------------------------------------------------
r either by notes (adenocarcinoma or large cell carcinoma) or by calling office.
Look for Alimta PAs. Combo with Alimta is NMN.
\par
*For ovarian, list all prior therapies.NO\par
}
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Mi
crosoft Sans Serif;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs17 NO - SOC does not apply to this medication\f1\par
}
I AM LOOKING FOR:
------------------------
*Paste policy criteria and match with chart notes.
*TECH MAY APPROVE: If for eye (diabetic retinopathy, macular degeneration, retin
al vein occlusion, mention of intravitreal use). Please refer to the VEGF job ai
d.
*TECH MAY APPROVE: If metastatic (or stage IV) colorectal cancer and will be giv
en with 5FU therapy (FOLFOX, FOLFIRI, CAPEOX, Xeloda).
*For lung cancer, please verify if nonsquamous type of non small cell lung cancer.
|
|
|
|
Re: Extracting just text from a Blob Column [message #671238 is a reply to message #671198] |
Fri, 17 August 2018 18:28 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
If you have Oracle Text licensed and installed:
drop table tbl purge
/
create table tbl(
rtf blob
)
/
create index tbl_idx1
on tbl(rtf)
indextype is ctxsys.context
/
insert
into tbl
values(
utl_raw.cast_to_raw('{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 *Paste policy criteria and match with chart notes.\par*TECH MAY APPROVE: If for eye (diabetic retinopathy, macular degeneration, retinal vein occlusion, mention of intravitreal use). Please refer to the VEGF job aid.\par*TECH MAY APPROVE: If metastatic (or stage IV) colorectal cancer and will be given with 5FU therapy (FOLFOX, FOLFIRI, CAPEOX, Xeloda).\par*For lung cancer, please verify if nonsquamous type of non small cell lung cancer either by notes (adenocarcinoma or large cell carcinoma) or by calling office. Look for Alimta PAs. Combo with Alimta is NMN.\par*For ovarian, list all prior therapies.NO\par}{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Microsoft Sans Serif;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 NO - SOC does not apply to this medication\f1\par}')
)
/
commit
/
set serveroutput on
declare
v_text clob;
v_rowid rowid;
begin
select rowid
into v_rowid
from tbl;
ctx_doc.filter(
'tbl_idx1',
v_rowid,
v_text,
true
);
dbms_output.put_line(
regexp_replace(
v_text,
chr(10) || '+',
chr(10)
)
);
end;
/
*Paste policy criteria and match with chart notes.
*TECH MAY APPROVE: If for
eye (diabetic retinopathy, macular degeneration, retinal vein occlusion, mention
of intravitreal use). Please refer to the VEGF job aid.
*TECH MAY APPROVE: If
metastatic (or stage IV) colorectal cancer and will be given with 5FU therapy
(FOLFOX, FOLFIRI, CAPEOX, Xeloda).
*For lung cancer, please verify if
nonsquamous type of non small cell lung cancer either by notes (adenocarcinoma
or large cell carcinoma) or by calling office. Look for Alimta PAs. Combo with
Alimta is NMN.
*For ovarian, list all prior therapies.NO
PL/SQL procedure successfully completed.
SQL>
Other than that, you can get JAVA RTF to text converters (e.g. rtf-plaintext-extractor from github) and create Java wrapper stored function.
SY.
|
|
|