ORACLE splitting string with numbers to use into IN clause

Question

I have a string field named CONCATENATEDFIELD in a table that returns the value '1,2,3'

I need to use this field right inside the IN clause:

SELECT ID FROM TABLE WHERE STRINGFIELD IN (CONCATENATEDFIELD)

I need this to work like:

SELECT ID FROM TABLE WHERE STRINGFIELD IN ('1','2','3')

How can I make it works?

Answer 1

Parsing the concanetaed string might be painful, since, one way or another, it will require some kind of recursion.

You could use a regex instead:

where regexp_like(
    concatenated_field,
    '(^|,)' || string_field || (,|$)
)

Or simply like:

where ',' || concatenated_field || ',' like '%,' || string_field || ',%'

Side note: here is a recommended reading...

Answer 2

This CONCATENATEDFIELD is a string literal and can't be used with IN which expects a list of values (string values in your case).
Instead you can use the function INSTR():

SELECT ID FROM TABLE 
WHERE INSTSR(',' || CONCATENATEDFIELD || ',', ',' || STRINGFIELD || ',', 1, 1) > 0

Answer 3

You can use regexp_substr to divide this values in to rows which can be used in IN clause as following:

Where stringfield in 
  (select regexp_substr(CONCATENATEDFIELD,'[^,]+', 1, level) from dual
   connect by regexp_substr(CONCATENATEDFIELD, '[^,]+', 1, level) is not null)

Cheers!!