Re: select where for a specific pattern

From: Anurag Varma <avoracle_at_gmail.com>
Date: 10 Jul 2006 11:52:47 -0700
Message-ID: <1152557567.716740.211450@s13g2000cwa.googlegroups.com>

LHradowy wrote:
> Anurag Varma wrote:
> > LHradowy wrote:
> > > I have a column that I need to select on:
> > > Data looks like:
> > > 01 002 120
> > > 01 002 160
> > > 01 005 120
> > > 01 002 125
> > > 01 1 00 02 1
> > > 01 2 00 02 3
> > > 01 2 03 02 3
> > > 01 042 160
> > > 01 2 06 02 3
> > > 01 202 167
> > > etc.
> > >
> > > I need to only pull this type of format:
> > > ## ### ###
> > >
> > > select col1 from table1 where col1 like??
> > >
> > > Next question:
> > > How to update, this type of format:
> > > ## ### ###
> > >
> > > To this type of format:
> > > ## # ## ## #
> >
> > What oracle version?
> > What have you tried so far?
> >
> > Anurag
>
> I have been able to parse the data as I want it to look but do not know
> how to update them now.
>
> select tn,oe "oe",substr(oe,1,2)||' '||substr(oe,4,1)||' '||
> substr(oe,5,2)||' '||substr(oe,8,2)||' '||substr(oe,10,1) "formatted
> oe"
> from vt_mlr
> where oe like '__ ___ ___'
> and BLD ='WNPGBLD'
> ;

hmm .. still no oracle version.

You pretty much have a solution for 9i?

Try this:

update (

         select oe ,substr(oe,1,2)||' '||substr(oe,4,1)||' '||
          substr(oe,5,2)||' '||substr(oe,8,2)||' '||substr(oe,10,1)
fmt_oe
         from vt_mlr
         where oe like '__ ___ ___'
         and BLD ='WNPGBLD'

) t
set oe = fmt_oe
/

?

Although I'd rather have the like clause as:

where translate(oe,'0123456789','##########') = '## ### ###'

... in 10g the solution becomes easier with regexp_like and regexp_replace

Anurag Received on Mon Jul 10 2006 - 13:52:47 CDT