SQL find number inside a Comma seperated string

Question

I'm trying to find a number (ID) inside Comma seperated string. I tried it with a Select but didn't work. Is there another Method I could use?

SELECT CASE WHEN TO_CHAR(1) IN ('1, 2, 3, 4') THEN 1 ELSE 0 END AS Result
FROM DUAL

I always get '0' . Can anyone please help here.

Answer 1

Such query can convert for you comma separated list into table:

select regexp_substr('1,2,3,4','[^,]+', 1, level) val from dual
  connect by regexp_substr('1,2,3,4', '[^,]+', 1, level) is not null;

Then you can perform in on result of the query.

Another nice option is:

select to_number(column_value) as IDs from xmltable('1,2,3,4,5');