you have group bys and aggregates. you always want to avoid that if
possible. performance implications. only use when absolutely necessary.
"Prince Kumar" <gspk_at_yahoo.com> wrote in message
news:629275ba.0312221634.3b544646_at_posting.google.com...
> Would something like the following work for you?
>
> variable l_v varchar2(120);
>
> exec :l_v := '276/N-BSFPO-159/NE-USMIA-770/W1-PAMAN-792/U'
>
> select substr(:l_v,odd +1 ,eve - odd - 1) from
> (
> select max(decode(mod(r,2),1,pos,null)) odd,
> max(decode(mod(r,2),0,pos,null)) eve from (select rownum r,
> instr(:l_v,'-',1,x.rn) pos from (select rownum rn from all_tables
> where rownum < 15) x ) group by round(r/2)
> )
>
> Prince.
>
> rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message
news:<1efdad5b.0312221159.1bf11a55_at_posting.google.com>...
> > chse30_at_hotmail.com (Christian Svensson) wrote in message
news:<ccc2a7eb.0312220517.5e896a5c_at_posting.google.com>...
> > > Greetings all,
> > >
> > > I wonder if any Oracle SQL gurus out there have any smart ways of
> > > solving this string problem.
> > >
> > > We have in a column this kind of example data:
> > >
> > > 276/S-BSFPO-159/N
> > > 154/NE-PAMAN-061/E-BSFPO-276/S
> > > 276/N-BSFPO-159/NE-USMIA-770/W1-PAMAN-792/U
> > >
> > > I want to find the strings that are between the "-" characters. For
> > > example in the string 276/N-BSFPO-159/N I want to get BSFPO. In the
> > > string 276/N-BSFPO-159/N-USMIA-770/W-PAMAN-792/U I want to get BSFPO
> > > and USMIA and PAMAN.
> > >
> > > Then I want to use these codes to be used in a WHERE statement, for
> > > example:
> > >
> > > WHERE RKST_CODE IN ('BSFPO','USMIA','PAMAN')
> > >
> > > As you can see, sometimes there will be just one code, and sometimes
> > > there will be 3 codes to find (up to 7 codes max).
> > >
> > > I can not use substrings with fixed position, so my current solution
> > > is to use instr to get positions of "-" character and then use that as
> > > an input to substr. But it will be a verry messy/hard-to-understand
> > > code.
> > >
> > > Does anyone out there have any better smart ideas of how to solve this
> > > ?
> > >
> > > Any url:s would be great.
> > >
> > > Thanks and merry Christmas !
> > >
> > > /Christian
> >
> >
> > in 8i. next instr inside of a substr of the form(syntax may be wrong)
> >
> > select substr(col,instr(col,'-',1,1),instr(col,2,1))
> > from table;
> >
> > with 9i. just use a case statement with a like. syntax is on otn. in
> > 8i you cant wrap case statements inside of pl/sql. if this is a
> > straight sql statement without pl/sql then you can use case
> > statements. same functionality. easier to read and maintain.
> >
> >
> > to get the 'in' list. go to asktom.oracle.com and do a search for
> > 'variable in-list' you have to make an object in the database then
> > cast it.
Received on Mon Dec 22 2003 - 18:47:42 CST
