Oracle SQL comma separated parameter in where clause

Question

My table looks like:

+----------+----------+
|stud_id   |stud_name |
+----------+----------+
|101       |A         |
|102       |B         |
|103       |C         |
|104       |D         |
|105       |E         |
+----------+----------+

My query

SELECT * FROM students_tbl stud WHERE stud.stud_id IN (:s_id);

When I input 102 it returns:

+----------+----------+
|stud_id   |stud_name |
+----------+----------+
|102       |B         |
+----------+----------+

just as expected.

Is there a way where I can input multiple values like 102,103,104 and results like this?:

+----------+----------+
|stud_id   |stud_name |
+----------+----------+
|102       |B         |
|103       |C         |
|104       |D         |
+----------+----------+

Very similar to https://stackoverflow.com/q/57753759/1509264 — MT0, Sep 09 '19 at 08:16

score 2 · Accepted Answer · answered Sep 09 '19 at 07:23

2

You can use regexp_substr with connect by for it.

SELECT * FROM students_tbl stud WHERE stud.stud_id IN 
(Select regexp_substr(:s_id,'[^,]+', 1,level)
From dual
Connect by regexp_substr(:s_id,'[^,]+', 1, level) is not null);

Cheers!!

answered Sep 09 '19 at 07:23

Popeye

35,427
4
10
31

score 1 · Answer 2 · answered Sep 09 '19 at 08:14

1

You don't need to split the value of the bind variable; a faster solution is to see if the id (plus surrounding delimiters) is a sub-string of the bind variable (with leading and trailing delimiters):

select *
from   students_tbl
where  ','||:s_id||',' LIKE ','||stud_id||','

answered Sep 09 '19 at 08:14

MT0

143,790
11
59
117

I'd just point out that this construct prevents you from using an index on `stud_id`. Probably not critical if the table has a few hundred students. If the table has a few hundred thousand students, though, that's going to get expensive. – Justin Cave Sep 09 '19 at 17:17
If you really wanted to you could pass `:s_id` as a `TABLE OF NUMBER` and use `WHERE stud_id MEMBER OF :s_id`. – MT0 Sep 09 '19 at 17:24

score 0 · Answer 3 · edited Sep 09 '19 at 08:39

0

Below query will ask you for 3 input values and you will get desired result

SELECT * FROM students_tbl stud WHERE stud.stud_id IN (:s_id,:s_id1,:s_id2);

edited Sep 09 '19 at 08:39

barbsan

3,418
11
21
28

answered Sep 09 '19 at 07:24

CMK

40
2
10

Oracle SQL comma separated parameter in where clause

3 Answers3

Linked

Related