Re: Smart SQL function wanted

Christian Svensson wrote:

> 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

Redesign the app.
You are having information in code -don't. Normalize! [bis[bis]]

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel