Smart SQL function wanted

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 Received on Mon Dec 22 2003 - 07:17:02 CST