Re: Smart SQL function wanted

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:34:36 CST