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

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.

Comments:

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

Posted by Kieron Alsmith on December 13, 2011 at 02:42 PM IST #

Great!!! very helpful!!

Posted by guest on February 17, 2012 at 04:31 AM IST #

thank you very much

Posted by guest on February 22, 2012 at 12:57 PM IST #

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.

Posted by guest on February 24, 2012 at 06:13 PM IST #

Nice.

Posted by guest on March 02, 2012 at 06:10 AM IST #

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

Posted by guest on March 02, 2012 at 08:00 AM IST #

This is excellent! Thank you so much!

Posted by guest on March 05, 2012 at 01:46 PM IST #

Superb Article...

Posted by guest on March 06, 2012 at 07:34 PM IST #

3hours of trying by myself ... nothing
5minutes of googling and ... it's working

Thanks a lot!

Posted by Nibiru on March 22, 2012 at 04:43 PM IST #

Thanks!!

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

Posted by guest on March 22, 2012 at 08:15 PM IST #

Thanks this is very helpful!!

Posted by hretickqa on April 11, 2012 at 10:51 AM IST #

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

Posted by guest on April 17, 2012 at 06:20 PM IST #

Great !! Thanx a lot !!

Posted by guest on May 06, 2012 at 12:29 PM IST #

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

Posted by guest on May 23, 2012 at 02:35 AM IST #

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

Posted by guest on May 25, 2012 at 01:46 AM IST #

T_____T Very very thanks!! Life Long! xD

Posted by Yigo on May 31, 2012 at 08:41 PM IST #

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

Thanks, Dave Martin, Ether Solutions.

Posted by guest on June 26, 2012 at 08:22 PM IST #

Thanks......

Posted by Sushil on June 28, 2012 at 02:11 PM IST #

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

Posted by guest on June 30, 2012 at 03:42 AM IST #

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 #

>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

Posted by Arun on July 02, 2012 at 02:44 PM IST #

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.

Posted by Josh Bode on July 02, 2012 at 03:42 PM IST #

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.

Posted by Josh Bode on July 02, 2012 at 06:17 PM IST #

Never seen anything like it. This is awesome.

Posted by guest on July 09, 2012 at 07:34 PM IST #

This made my life a lot easier, thanks.

Posted by guest on July 19, 2012 at 03:12 AM IST #

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.

Posted by Narendra on July 25, 2012 at 03:04 AM IST #

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

Posted by chandu on July 31, 2012 at 06:38 PM IST #

Thank you; this is very useful.

Posted by Tom M on August 03, 2012 at 04:45 AM IST #

What they said - cheers dude!

Posted by guest on August 10, 2012 at 04:22 PM IST #

Thank you! This post was very helpful for me!

Posted by guest on August 16, 2012 at 05:54 PM IST #

Very simple to use. Excellent!!

Posted by Ravindra on August 31, 2012 at 12:21 PM IST #

Thanks. It was really helpful

Posted by Sameh Ismail on September 11, 2012 at 02:22 PM IST #

Hi,
I believe this code supports strings upto 4000 bytes only.
How to make it work for at least 8000 byte string?

Posted by guest on September 12, 2012 at 06:04 PM IST #

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

Posted by guest on September 19, 2012 at 12:21 AM IST #

Completely helpfull

Posted by JEREMY on September 22, 2012 at 07:41 AM IST #

It2s very useful. Thank you so much.

Posted by Onder on October 03, 2012 at 07:36 PM IST #

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

Posted by guest on October 05, 2012 at 05:11 PM IST #

Finally I got this fixed saved me some grey hairs.

Posted by Damien on October 06, 2012 at 05:14 AM IST #

Excellent work

Posted by guest on October 09, 2012 at 03:31 PM IST #

Awsome solution for parsing the string..

Posted by sandeep on October 18, 2012 at 04:10 PM IST #

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

Posted by guest on October 25, 2012 at 05:40 PM IST #

Great, Very helpful.

Posted by guest on October 26, 2012 at 08:35 PM IST #

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

Posted by MR on October 30, 2012 at 05:14 PM IST #

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

Posted by Nigel on November 01, 2012 at 01:52 AM IST #

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

Posted by John on November 13, 2012 at 03:29 AM IST #

so helpful!
Thank you so much!

Posted by Francesco on November 16, 2012 at 05:00 PM IST #

great... thank you.

Posted by Hari on November 21, 2012 at 09:21 PM IST #

Thanks!

Posted by guest on November 23, 2012 at 03:54 PM IST #

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.

Posted by Niranjan on December 05, 2012 at 04:33 PM IST #

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

Posted by guest on December 06, 2012 at 02:29 PM IST #

In case of Oracle 9i Database, REGEXP_SUBSTR will not work.
does any one have a solution for 9i ?

Posted by guest on January 11, 2013 at 02:45 AM IST #

Thanks :)

Posted by guest on January 15, 2013 at 06:57 PM IST #

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.

Posted by MR on January 17, 2013 at 02:01 AM IST #

Thanks so much!!

Posted by guest on February 05, 2013 at 07:47 AM IST #

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

Posted by guest on February 07, 2013 at 09:02 PM IST #

Excellent. Thanks for the knowledge.

Posted by guest on February 13, 2013 at 09:58 AM IST #

Excellent, thanks

Posted by guest on February 27, 2013 at 08:37 PM IST #

Great piece of code !
Exactly what I was looking for all along!
Thank you!!

Posted by guest on April 05, 2013 at 04:10 PM IST #

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!

Posted by guest on April 12, 2013 at 10:13 PM IST #

THanks. this was very very usefull to me

Posted by guest on April 25, 2013 at 11:12 AM IST #

Thanks for posting this. It is really helpful.

Posted by guest on May 02, 2013 at 03:47 AM IST #

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

Posted by guest on May 09, 2013 at 05:45 PM IST #

Worked great for us. Thanks for the great example!

Posted by guest on June 06, 2013 at 07:26 PM IST #

Many thanks

Posted by guest on July 10, 2013 at 04:12 PM IST #

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

Posted by guest on July 26, 2013 at 03:16 PM IST #

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

Posted by Praveena on August 21, 2013 at 02:04 AM IST #

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

Posted by Praveena on August 21, 2013 at 02:06 AM IST #

thanks :)

Posted by guest on August 26, 2013 at 03:37 PM IST #

Thank you!

Posted by guest on August 28, 2013 at 11:55 PM IST #

Another answer to the same problem using XMLDB not Regex

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

Posted by guest on September 14, 2013 at 02:25 PM IST #

It's a very helpful. Saving a lot of time spent!!
Keep posting big guys.

Posted by guest on October 24, 2013 at 03:58 PM IST #

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

Posted by guest on October 31, 2013 at 08:47 PM IST #

Chapeau!

Posted by guest on November 12, 2013 at 03:23 AM IST #

Wow, this works like a charm!!!

Posted by David Liu on November 19, 2013 at 09:14 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Tips & Tricks from Arun on JDev ADF, Forms, SQL & PL/SQL.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today