Re: REGEXP Functions in 10g (10.2.0.2.0)

From: admin <admin_at_rbtron.com>
Date: Tue, 11 Aug 2009 08:33:20 -0700 (PDT)
Message-ID: <4d292097-8e90-4da7-8f85-79556df55c8b_at_f37g2000yqn.googlegroups.com>

On Aug 7, 12:52 pm, "stevedhow..._at_gmail.com" <stevedhow..._at_gmail.com> wrote:
> On Aug 7, 11:55 am, admin <ad..._at_rbtron.com> wrote:
>
>
>
>
>
> > On Aug 7, 7:08 am, "stevedhow..._at_gmail.com" <stevedhow..._at_gmail.com>
> > wrote:
>
> > > On Aug 6, 4:40 pm, admin <ad..._at_rbtron.com> wrote:
>
> > > > Hi,
>
> > > > Is there any way to do remove a few lines from a paragraph using
> > > > regexp functions?
>
> > > > Something like : sed -e '/temp/,/test/d' text.txt ?
>
> > > > This is what I need:
>
> > > > select 'text' title from dual union select 'test' from dual union
> > > > select 'temp' from dual union select 'doc' from dual;
>
> > > > TITLE
> > > > --------------------------
> > > > doc
> > > > temp
> > > > test
> > > > text
>
> > > > Required Output after running regexp functions:
>
> > > > TITLE
> > > > --------------------------
> > > > doc
> > > > text
>
> > > > ie., remove anything between 'temp' and 'test' (inclusive) ...
>
> > > > Thanks
>
> > > > -Ad
>
> > > How about...
>
> > > SQL> select title from (
> > > select 'text' title from dual
> > > union
> > > select 'test' from dual
> > > union
> > > select 'temp' from dual
> > > union
> > > select 'doc' from dual)
> > > where not regexp_like(title,'^te[m-s]')  2    3    4    5    6    7
> > > 8    9  ;
>
> > > TITL
> > > ----
> > > doc
> > > text
>
> > > SQL>- Hide quoted text -
>
> > > - Show quoted text -
>
> > Steve,
>
> > I need it in the select clause itself not in the where condition ...
> > it is actually just 1 row, not 4 separate rows ... probably my "select
> > union" stuff gave you wrong info ... sorry about that ...
>
> > Mladen,
>
> > Yeah, it is a CLOB column, that I need to "delete" certain lines (not
> > rows as there is only 1 row) that start with "temp" and end with
> > "test" and anything in between.
>
> > I can spool it out and run sed -e '/temp/,/test/d' text.txt to get the
> > desired output. I was just wondering if it is possible from within
> > Oracle itself, since 10g now supports a ton of functions ...
>
> How about...
>
> variable title varchar2(100)
> exec :title := 'text' || chr(10) || 'test' || chr(10) || 'temp' || chr
> (10) || 'doc'
> select regexp_replace(:title,'te[m-s].+' || chr(10),'') from dual
>
> That could get messy with languages, though.  Hopefully it is a start
> for you.
>
> Regards,
>
> Steve- Hide quoted text -
>
> - Show quoted text -

Thanx Steve, it works, almost :)

Not all words start with "te" ... so I can't use "te" to identify the lines ... it needs to be generic ...

Say,

exec :title := 'test1' || chr(10) || 'doc1' || chr(10) || 'temp1' || chr (10) || 'test2' || chr(10) || 'doc2' || chr(10) || 'temp2'|| chr (10) || 'test3'

I need to cut between doc1 and test2, which means the output needs to be :

test1
doc2
temp2
test3

Thanx

-Ad Received on Tue Aug 11 2009 - 10:33:20 CDT