Creating an IN-clause from a comma separated string

I was looking for a simple way to pass a comma separated string as a bind variable to a SQL expression. Until now, I used a string tokenizer function building a VARRAY which I wrapped with the TABLE-function.

Today, I found this blog post with a really simple approach. It looks so obvious and was so hard to find. Just use a regular expression for extracting the string between a line start and a comma or between a comma and a comma. Repeat this with a connect by clause as long as there are occurrences of this pattern. The level keyword is the iteration counter and defines which occurrence to extract.

The pattern is:

SELECT *
FROM
WHERE
    <field/value> IN
        (
            SELECT regexp_substr(:list,'[^,]+', 1, level) items
            FROM dual
            CONNECT BY regexp_substr(:list, '[^,]+', 1, level) is not null
        );

It is rather easy to extend this pattern for variable separators:

SELECT *
FROM
WHERE
  <field/value> IN
    (
      SELECT regexp_substr(:list,'[^' || :separators || ']+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:list, '[^' || :separators || ']+', 1, level) is not null
    );

Each character in the :separators bind variable is a separator in the :list bind variable.

In the following example the list contains words separated by dots and commas. We want to show all records in dual when ‚foo‘ is in the list.

variable separators varchar2(2);
variable list varchar2(20);

exec :separators := ',.'
exec :list := 'foo.bar,foo.bar';

SELECT *
FROM dual
WHERE
  'foo' IN
    (
      SELECT regexp_substr(:list,'[^' || :separators || ']+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:list, '[^' || :separators || ']+', 1, level) is not null
    );

Sometimes, the separators are followed by whitespaces (e.g. for improving readability). Removing them with the TRIM-function is easy:

variable separators varchar2(2);
variable list varchar2(20);

exec :separators := ',.'
exec :list := 'foo .bar,foo1.bar1';

SELECT *
FROM dual
WHERE
  'foo' IN
    (
      SELECT trim(regexp_substr(:list,'[^' || :separators || ']+', 1, level)) items
      FROM dual
      CONNECT BY regexp_substr(:list, '[^' || :separators || ']+', 1, level) is not null
    );

References

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

3 Gedanken zu „Creating an IN-clause from a comma separated string“

  1. Since in your case it’s much faster, that must meen that the PL/SQL overhead of converting the string is much lower than the time burned up in re-parsing the query with the embedded string literals every time? The only difference in your two versions of the query is the parsing of string litereals vs. parsing of a string to bind variables, right?

  2. Quite often, we want to pass a parameter to the bind variable in the VO’s query with a comma separated value, for the where clause with an IN operator. However, normal SQL query that the VO contain interpret that whole comma separated value as a single String and our usecase fail to get fulfilled.Ex. question in OTN thread : https://forums.oracle.com/forums/thread.jspa?messageID=10125366To overcome this, Mohammad Jabr has written a blog entry with the help of Steve Muench’s example #126. This has been achieved by using a TYPE and CASTing it to get the list.In this article, we’ll see another option to alter the query using regexp_substr , without having to use a TYPE and CAST.Let us take an example of a VO created using EMP table with the following query.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Time limit is exhausted. Please reload the CAPTCHA.