How to use comma separated value from column to IN clause

Question

I have a table, which contains below values(small snap of data):

Row#| Column A | Column B
--------------------------
1   |A         | A,B
2   |C         | A,D
3   |M         | Z
4   |C         |A,B,L
5   |F         |F
6   |C         |O

List goes on and on. Column A and B is populated by other application, so its combinations are not predictable. Table has more than 4000 rows.

My search values are not fixed; I have to use search value from Column B of same row for which I am looking for data in Column A.

I want to check Column A's value is IN Column B or not. Basically compare Column A with B, if find match in B result should skip that row.

In above case Row#1's Column A's value is in B which is not true for row#2.

My query should reach all rows and compare Column A with B. Query should return Row#1 and 5

This question is different Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL As it has fixed set of search value

I dont have any solution, I tried regexpr with connect but I didnt get desired output. I don't have any clue to resolve it — Soni Snehal, Dec 05 '16 at 20:59

Gurwinder Singh · Accepted Answer · 2016-12-05T20:15:58.510

1

select * from my_table where instr(columnB, columnA) > 0;

Example: Should contain

select * from (
    select 1 a, 'A' b, 'A,B' c from dual union all
    select 2, 'C', 'A,D' from dual ) t
where instr(c,b) > 0;

Result:

A   B   C
-----------
1   A   A,B

Example 2: Shouldn't contain

select * from (
    select 1 a, 'A' b, 'A,B' c from dual union all
    select 2, 'C', 'A,D' from dual ) t
where instr(c,b) = 0;

Result:

A   B   C
-----------
2   C   A,D

edited Dec 05 '16 at 20:15

answered Dec 05 '16 at 19:32

Gurwinder Singh

38,557
6
51
76

It didnt work, with this command it didnt return me anything. My expected output is Row#2 – Soni Snehal Dec 05 '16 at 19:48
use `select * from my_table where instr(columnB, columnA) = 0;` to see that – Gurwinder Singh Dec 05 '16 at 19:50
That also didnt work. I tried few values, -1, 0 ,1 but dint worked. – Soni Snehal Dec 05 '16 at 19:57
*didnt work* how? You get an error? what do you see? – Gurwinder Singh Dec 05 '16 at 20:00
It gives me no rows in result. As per above table data, my query should return Row#2, but with instr, result has 0 row – Soni Snehal Dec 05 '16 at 20:06
See my edited answer – Gurwinder Singh Dec 05 '16 at 20:17
In example I mentioned only two rows, in table I have total 4k plus rows, each row has different values. Column B have different combinations of A-Z and sometime single alphabet too. – Soni Snehal Dec 05 '16 at 20:19
@Snehal my solution works for the examples you gave. Please update your question with example for which it doesn't work. – Gurwinder Singh Dec 05 '16 at 20:22
Your edited answer is not feasible for me as I have 4K plus rows. I can not union all my rows. Basically I have to match Column A's data with Columns B data, if Column B contains alphabet of Column A skip that row in result, else show mismatched columns. – Soni Snehal Dec 05 '16 at 20:36
What? no. that is just for example. – Gurwinder Singh Dec 05 '16 at 20:38
As I said post the data in question on which it doesnt work – Gurwinder Singh Dec 05 '16 at 20:39
Hope I am more clear now. I have no control on Column A and B. Their values are not predictable and fixed. If I go with your approach I have to write 4000 select statements. I need query with render Column B's data as separate Alphabet which I can pass in "IN" clause to compare with Column A – Soni Snehal Dec 05 '16 at 20:50
Your answer was correct, due to environment issue I was not getting data. – Soni Snehal Dec 05 '16 at 21:37

How to use comma separated value from column to IN clause

1 Answers1