Re: Smart SQL function wanted

| > 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).
| >

if you can't redesign as Frank suggested (not always possible to trade in the yugo on a benz!), then you need to do one of the following

1. create a text index of some sort on your column with the embedded codes (oracle context or home-grown)
2. if that is not possible (and that is the best solution from a performance standpoint), at least encapsulate your formula in a stored function -- which will give you more maintainable code

Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting Received on Wed Dec 24 2003 - 07:16:22 CST