X

How to split comma separated string and pass to IN clause of select statement

Arunkumar Ramamoorthy
Consulting Technical Manager

In some cases, we get a comma separated string as output (say from another select statement) that we would need to pass to the IN clause of a select statement.




This article explains how to achieve that using regexp_substr (DB >=10g).




For example, assume a select statement returns the following


'SMITH,ALLEN,WARD,JONES'

Now, we would need to pass this to another select statement as IN clause and get the output.


SQL> select * from emp where ename in ('SMITH,ALLEN,WARD,JONES');

no rows selected

Well, this is not our expected output. We expect the query to return 4 rows.




This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause.




Oracle provides regexp_substr function, which comes handy for this scenario.




First, we will form a query, that splits this comma separated string and gives the individual strings as rows.


SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
2 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;


REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.




We can pass this query to our select statement to get the desired output.


SQL> select * from emp where ename in (
2 select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
3 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20

Now, the query returns what we expected.

Join the discussion

Comments ( 74 )
  • Kieron Alsmith Tuesday, December 13, 2011

    I'm surprised there aren't dozens of comments on this. I'm working with two, ancient, web-based reporting tools (you know, pre-Y2K) that can't even parse Strings for themselves.

    This little SQL snippet saved my life.

    Thank you!!

    - Kieron


  • guest Thursday, February 16, 2012

    Great!!! very helpful!!


  • guest Wednesday, February 22, 2012

    thank you very much


  • guest Friday, February 24, 2012

    This is a fantastic simple query that gave a result for which I have been struggling for over weeks now.

    Hats Off to the maestro who has explained it.


  • guest Friday, March 2, 2012

    Nice.


  • guest Friday, March 2, 2012

    Wow! Thanks. This is the cleanest solution I've found to work with the comma separated list in an IN clause....


  • guest Monday, March 5, 2012

    This is excellent! Thank you so much!


  • guest Tuesday, March 6, 2012

    Superb Article...


  • Nibiru Thursday, March 22, 2012

    3hours of trying by myself ... nothing

    5minutes of googling and ... it's working

    Thanks a lot!


  • guest Thursday, March 22, 2012

    Thanks!!

    Luckily I searched first before trying it myself. I would never have come up with this.


  • hretickqa Wednesday, April 11, 2012

    Thanks this is very helpful!!


  • guest Tuesday, April 17, 2012

    THANK YOU SO SO MUCH!!!!!!!!!


  • guest Sunday, May 6, 2012

    Great !! Thanx a lot !!


  • guest Tuesday, May 22, 2012

    very helpful, thanks a lot, nice solution, I looked for hours in google, all of the solutions are very complicated, like creating types in Oracle, lost of code, etc, this is very efficient, thanks


  • guest Thursday, May 24, 2012

    wow this is fantastic. Quite possibly the most elegant solution that I have seen for this problem.


  • Yigo Thursday, May 31, 2012

    T_____T Very very thanks!! Life Long! xD


  • guest Tuesday, June 26, 2012

    This is great and a real benefit to anybody building custom components for Oracle WebCenter Content.

    Thanks, Dave Martin, Ether Solutions.


  • Sushil Thursday, June 28, 2012

    Thanks......


  • guest Friday, June 29, 2012

    This a beautiful, and beautifully simple, solution to this issue. Thank you!


  • Josh Bode Sunday, July 1, 2012

    Another way would be to use INSTR()

    e.g.

    SELECT e.*

    FROM emp e

    WHERE INSTR('|SMITH|ALLEN|WARD|JONES|', '|' || e.ename || '|') > 0;


  • Arun Monday, July 2, 2012

    >Another way would be to use INSTR()

    >e.g.

    >SELECT e.*

    >FROM emp e

    >WHERE INSTR('|SMITH|ALLEN|WARD|JONES|', '|' || e.ename || '|') > 0;

    >Posted by Josh Bode on July 02, 2012 at 05:29 AM IST

    @Josh, this is good one :). One can even reduce it by removing a preceding and trailing | and change the delimiter to ,

    Something like

    SELECT e.*

    FROM emp e

    WHERE INSTR('SMITH,ALLEN,WARD,JONES', e.ename ) > 0


  • Josh Bode Monday, July 2, 2012

    Hi Arun, the wrapping symbols can only be removed if your enames don't share substrings. For example, someone with the name of SMIT or LEN would match in your modification.


  • Josh Bode Monday, July 2, 2012

    Something like the following will also work without having to be careful around issues of substring matches.

    SELECT e.*

    FROM emp e

    WHERE REGEXP_LIKE(e.ename, '^(SMITH|ALLEN|WARD|JONES)$')

    The ^ and $ in the regular expression guarantees that it only matches on whole names and not substrings.

    I think this solution is quite clear in terms of intention.


  • guest Monday, July 9, 2012

    Never seen anything like it. This is awesome.


  • guest Wednesday, July 18, 2012

    This made my life a lot easier, thanks.


  • Narendra Tuesday, July 24, 2012

    This is awesome stuff..!!!! since i am not an expert in PL/SQL programming this piece of code saved at least 2 hours for me..!! Thanks.


  • chandu Tuesday, July 31, 2012

    Helped me fix a tricky situation in our project. Thanks a lot.


  • Tom M Thursday, August 2, 2012

    Thank you; this is very useful.


  • guest Friday, August 10, 2012

    What they said - cheers dude!


  • guest Thursday, August 16, 2012

    Thank you! This post was very helpful for me!


  • Ravindra Friday, August 31, 2012

    Very simple to use. Excellent!!


  • Sameh Ismail Tuesday, September 11, 2012

    Thanks. It was really helpful


  • guest Wednesday, September 12, 2012

    Hi,

    I believe this code supports strings upto 4000 bytes only.

    How to make it work for at least 8000 byte string?


  • guest Tuesday, September 18, 2012

    I have to thank you. I was able to use in clause given a comma separated string of values. Special thanks again.


  • JEREMY Saturday, September 22, 2012

    Completely helpfull


  • Onder Wednesday, October 3, 2012

    It2s very useful. Thank you so much.


  • guest Friday, October 5, 2012

    I do not like REGEXP so I write similar script but on pure SQL

    declare

    ids varchar2(100) := '1,2,3,4,5';

    l_cnt number;

    begin

    select count(*)

    into l_cnt

    from account

    where account_id in

    (select TO_NUMBER(trim(SUBSTR(t1.str,

    t1.curr_pos + 1,

    DECODE(t1.next_pos,

    0,

    LENGTH(t1.str) + 2,

    t1.next_pos)

    - t1.curr_pos - 1)))

    from (select ids str,

    DECODE(level,

    1,

    0,

    INSTR(ids, ',', 1, level - 1)

    ) as curr_pos,

    INSTR(ids, ',', 1, level) as next_pos

    from dual

    connect by level <= LENGTH(ids)

    - LENGTH(

    replace(ids,

    ',',

    '')

    ) + 1) t1);

    dbms_output.put_line(l_cnt);

    end;

    example for selecting number from more that one srtings there

    http://sprogram.com.ua/en/articles/oracle-select-from-string-varchar2

    good luck


  • Damien Friday, October 5, 2012

    Finally I got this fixed saved me some grey hairs.


  • guest Tuesday, October 9, 2012

    Excellent work


  • sandeep Thursday, October 18, 2012

    Awsome solution for parsing the string..


  • guest Thursday, October 25, 2012

    Thanksarun very good logic, this is helps me to reduce my front end code


  • guest Friday, October 26, 2012

    Great, Very helpful.


  • MR Tuesday, October 30, 2012

    >>Another way would be to use INSTR()

    >

    >>e.g.

    >

    >>SELECT e.*

    >>FROM emp e

    >>WHERE INSTR('|SMITH|ALLEN|WARD|JONES|', '|' || e.ename || '|') > 0;

    >

    >>Posted by Josh Bode on July 02, 2012 at 05:29 AM IST

    >

    >@Josh, this is good one :). One can even reduce it by removing a preceding and trailing | and change the delimiter to ,

    >

    >Something like

    >

    >SELECT e.*

    >FROM emp e

    >WHERE INSTR('SMITH,ALLEN,WARD,JONES', e.ename ) > 0

    @Arun,

    removing the preceding and trailing will introduce a bug! (that query will find SMITH, SMIT, SMI, SM, S, etc.).

    On the query suggested by @Josh, the '|' can be replaced by ',' without problems, if the text to find don't have comma.

    Both solutions have performance problems, because there is no possible to use indexes, and the oracle will be forced to do a FULL TABLE SCAN (unless there are another condition on query, that use indexes)

    MR


  • Nigel Wednesday, October 31, 2012

    Brilliantly Simple !!! Thank You

    I needed to identify 'recurrence' days for a replicating roster and return calendar entries (dayofweek) that matched those days...

    INSTR(r.recurrence_days, c.dayofweek ) > 0

    The instr option was perfect :)


  • John Monday, November 12, 2012

    Very useful. Thanks for taking the time to post this hint.


  • Francesco Friday, November 16, 2012

    so helpful!

    Thank you so much!


  • Hari Wednesday, November 21, 2012

    great... thank you.


  • guest Friday, November 23, 2012

    Thanks!


  • Niranjan Wednesday, December 5, 2012

    Simply, Great,

    This saved my days work of creating separate procedure and using it.

    This works fine for passing comma separated value as parameter in concurrent program too.


  • guest Thursday, December 6, 2012

    Beautiful piece of code...implemented it to cross tabulate one of my HLR dumps


  • guest Thursday, January 10, 2013

    In case of Oracle 9i Database, REGEXP_SUBSTR will not work.

    does any one have a solution for 9i ?


  • guest Tuesday, January 15, 2013

    Thanks :)


  • MR Wednesday, January 16, 2013

    guest,

    if you are in oracle 9i, try look the post "I do not like REGEXP so I write similar script but on pure SQL" (Posted by guest on October 05, 2012 at 05:11 PM IST).

    I don't validate if the code is correct, but you can try it.


  • guest Tuesday, February 5, 2013

    Thanks so much!!


  • guest Thursday, February 7, 2013

    A-W-E-S-O-M-E!!!


  • guest Wednesday, February 13, 2013

    Excellent. Thanks for the knowledge.


  • guest Wednesday, February 27, 2013

    Excellent, thanks


  • guest Friday, April 5, 2013

    Great piece of code !

    Exactly what I was looking for all along!

    Thank you!!


  • guest Friday, April 12, 2013

    Thanks a lot for this solution! I was already very familiar with most regular expressions through my background in Perl, but somehow I never really explored the uses of negated character classes before.

    Thanks again for the quick solution, and for teaching an old regexp dog a new trick!


  • guest Thursday, April 25, 2013

    THanks. this was very very usefull to me


  • guest Wednesday, May 1, 2013

    Thanks for posting this. It is really helpful.


  • guest Thursday, May 9, 2013

    Awesome blog.... thank you so much for sharing..


  • guest Thursday, June 6, 2013

    Worked great for us. Thanks for the great example!


  • guest Wednesday, July 10, 2013

    Many thanks


  • guest Friday, July 26, 2013

    This does not handle empty segments:

    SELECT REGEXP_SUBSTR('SMITH,,WARD,JONES', '[^,]+', 1, 2)

    FROM dual

    returns WARD and not NULL.

    If every segment is preceeded by a comma, the following works:

    SELECT SUBSTR(REGEXP_SUBSTR(',SMITH,,WARD,JONES',',[^,]*', 1, 2), 2)

    FROM dual


  • Praveena Tuesday, August 20, 2013

    Thank you so much.

    I have wasted many hours figuring out how to achieve the task that this simply query is doing.

    It would be more helpful to make this posting easily searchable for everyone. It took a while for me to find it out.

    -Praveena


  • Praveena Tuesday, August 20, 2013

    Thank you so much.

    I have wasted many hours figuring out how to achieve the task that this simply query is doing.

    It would be more helpful to make this posting easily searchable for everyone. It took a while for me to find it out.

    -Praveena


  • guest Monday, August 26, 2013

    thanks :)


  • guest Wednesday, August 28, 2013

    Thank you!


  • guest Saturday, September 14, 2013

    Another answer to the same problem using XMLDB not Regex

    http://blog.maksoft.ch/2013/09/11/yet-another-oracle-xml-challenge/


  • guest Thursday, October 24, 2013

    It's a very helpful. Saving a lot of time spent!!

    Keep posting big guys.


  • guest Thursday, October 31, 2013

    Many thanks :). This saved a lot of work.


  • guest Monday, November 11, 2013

    Chapeau!


  • David Liu Tuesday, November 19, 2013

    Wow, this works like a charm!!!


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.