Home » SQL & PL/SQL » SQL & PL/SQL » Extracting just text from a Blob Column (11.2)
Extracting just text from a Blob Column [message #671189] Thu, 16 August 2018 11:30 Go to next message
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 #671192 is a reply to message #671189] Thu, 16 August 2018 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You need to show the raw data and the result you want from it.

Re: Extracting just text from a Blob Column [message #671194 is a reply to message #671189] Thu, 16 August 2018 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 26565
Registered: January 2009
Location: SoCal
Senior Member
dejah wrote on Thu, 16 August 2018 09:30
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
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Extracting just text from a Blob Column [message #671198 is a reply to message #671194] Thu, 16 August 2018 12:18 Go to previous messageGo to next message
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 #671199 is a reply to message #671198] Thu, 16 August 2018 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We are not interested in what you get with a function that does not return what you want.
What we need is:
- the input data
- the output you want from it
- an explanation of this output (what you don't want for instance)

Re: Extracting just text from a Blob Column [message #671238 is a reply to message #671198] Fri, 17 August 2018 18:28 Go to previous message
Solomon Yakobson
Messages: 2863
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.
Previous Topic: SQL to Subtract X workdays from a date
Next Topic: Prevent Rounding when using Format Masking
Goto Forum:
  


Current Time: Fri Jul 19 09:47:29 CDT 2019