Re: Smart SQL function wanted

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 - 13:59:09 CST