WHERE condition for "starts with any of the values in a CSV list"

Question

Possible Duplicate:
how to convert csv to table in oracle

I have a query in PL/SQL that is built to handle input in a variable as a "starts-with" filter:

WHERE product_group LIKE strProductGroup || '%'

As a new "feature", the input variable could contain comma separated values. So, where before I would expect something like "ART", I could now see "ART,DRM".

I'd like to avoid building this query as a string and using EXECUTE IMMEDIATE, if possible. Can anyone think of a way to write a WHERE condition that is the equivalent of saying "starts with any of the values in a CSV list" in Oracle 10g?

maybe you would like to use `product_group in (strProductGroup)`? — Pablo, Sep 19 '12 at 20:29
not a duplicate. the other post is asking about 11g and I also have the "starts-with" requirement. — Paul, Sep 19 '12 at 20:38

score 1 · Answer 1 · answered Sep 19 '12 at 20:30

Assuming that you don't have any restrictions on creating a couple of additional objects (a collection type and a function), you can parse the list into a collection that you can reference in your query. Tom Kyte has a good discussion on this in his variable "IN" list thread.

If you use Tom's myTableType and in_list function, for example

SQL> create or replace type myTableType as table 
     of varchar2 (255);
  2  /

Type created.


ops$tkyte@dev8i> create or replace 
     function in_list( p_string in varchar2 ) return myTableType
  2  as
  3      l_string        long default p_string || ',';
  4      l_data          myTableType := myTableType();
  5      n               number;
  6  begin
  7    loop
  8        exit when l_string is null;
  9        n := instr( l_string, ',' );
10         l_data.extend;
11         l_data(l_data.count) := 
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
12         l_string := substr( l_string, n+1 );
13    end loop;
14
15    return l_data;
16  end;
17  /

Then you can search for equality relatively easily.

WHERE product_group IN (SELECT column_value 
                          FROM TABLE( in_list( strProductGroup )))

But you want to do a LIKE which is a bit more challenging since you can't do a LIKE on an in-list. You could, however, do something like

select *
  from emp e,
       (select '^' || column_value search_regexp
          from table( in_list( 'KIN,BOB' ))) a
 where regexp_like( e.ename, a.search_regexp )

This will search the EMP table for any employees where the ENAME begins with either KIN or BOB. In the default SCOTT.EMP table, this will return just one row, the row where the ENAME is "KING"

@gpeche - It wouldn't hurt. Assuming the string fits in a `VARCHAR2`, though, it's limited to 32k. If each search term is 3 bytes with a 4th byte for the comma, that means there are a maximum of 8k terms. That's a pretty reasonable number of elements to have in a collection without needing to pipeline the results. It wouldn't hurt to pipeline the results, but it does add a touch of complexity. — Justin Cave, Sep 19 '12 at 20:59
Also, it is important to remember to have a look at the execution plan, as some Oracle versions get quite confused about the table type. Sometimes you need to add a `cardinality` or `materialize` hint to get Oracle to do the right thing. — gpeche, Sep 19 '12 at 21:07

score 0 · Accepted Answer · edited May 23 '17 at 12:03

I found another post that gave me an idea. In my specific case, the values in the input will all be 3 characters, so I can do the following:

AND SUBSTR(product_group, 0, 3) IN
    (SELECT regexp_substr(strProductGroup, '[^,]+', 1, LEVEL)
      FROM dual
      CONNECT BY LEVEL <= length(regexp_replace(strProductGroup, '[^,]+')) + 1)

I like this solution, because it does not require additional types or functions, but is pretty limited to my specific case.

WHERE condition for "starts with any of the values in a CSV list"

2 Answers2