Re: Smart SQL function wanted

From: Relational DBA <juha_teuvonnen_at_yahoo.com>
Date: 22 Dec 2003 12:33:32 -0800
Message-ID: <7f8bbb9e.0312221233.771d3ac8@posting.google.com>

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

Here's how I would attack this problem, assuming you want a quick and dirty hack:

1. Write a PL/SQL function that parses the string and returns the desired strings. A function can return only 1 string. You want multiple strings returned, so you will have to return a delimitered string wich concatenates all of them and returns something like ',BSFPO,USMIA,PAMAN,'

function my_function(str in varchar2)
return varchar2
is

    str1 varchar2(4000);
    strout varchar2(4000) :=',';
    tab dbms_utility.uncl_array;
    tablen binary_integer;
begin

    str1 := replace(str,'-',',');
    if instr(str,'-') >0
    then

        dbms_utility.comma_to_table(str1,tablen,tab);
        for i in 1..tablen 
        loop 
            strout := strout||tab(i)||',';
        end loop;

    end if;
    return strout;
end;

2. Call this function from the SQL statement:

 ...

WHERE instr( (my_function(my_column), ',BSFPO,') > 0
   OR instr( (my_function(my_column), ',USMIA,') > 0
   OR instr( (my_function(my_column), ',PAMAN,') > 0

CAVEAT:
1. The code above presumes that the text of my_column has no commas. 2. The solution above will not be very fast. If you run into performance issues, you should probably consider using InterMedia text or parsing your data before it gets into the database column and storing it in a normalized manner. Having the data normalized is usually a better design and has various advantages. I am guessing that you have a 1 to many relationship, and you'd be better off with the following structure:

col1 col2 col3 col4 col5

---

276    S      BSFPO   159    N
154    NE     PAMAN   061
154    E      BSFPO   276    S
276    N      BSFPO   159
276    NE     USMIA   770
276    W1     PAMAN   792    U

Received on Mon Dec 22 2003 - 14:33:32 CST