SELECT IN query using returned value as CSV

Question

I am trying to use the results of a subquery in an IN query to return some results from a table based on some ids in a second table. The structure of Table1 is not important, but Table2 stores information like this:

other_ID  | members
1         | .10.,.16.,.86.
2         | .32.,.34.

other_ID is stored as INT while members is stored as TEXT. I didn't create the table structure, but my understanding is that members is stored in that format since it gets tricky storing variable-length CSVs in columns in a database. Either way, I'm not able to change the table structure.

I have the following query:

SELECT * FROM Table1 WHERE
Table1.ID IN (REPLACE((SELECT members FROM Table2 WHERE other_ID = 2), ".", ""));

Try as I might, the query only ever returns values from the first value in the members column (i.e. 32). If I try the following query:

SELECT * FROM Table1 WHERE Table1.ID IN (32,34);

The results are returned successfully.

Any ideas as to what I'm doing wrong?

Answer 1

You could try using REGEXP here with a join:

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.members REGEXP CONCAT('[[:<:]]', t1.ID, '[[:>:]]')
WHERE
    t2.other_ID = 2;

The regex logic used above would compare this members string:

.32.,.34.

against a regex which looks like this:

\b34\b

This should work, as the commas/dots surrounding each number in members form a word boundary.

Note that the best long term fix here would be to not store your data in unnormalized CSV format.

Answer 2

REPLACE((SELECT members FROM Table2 WHERE other_ID = 2), ".", "")

this will not work because it will take as single number you have to split that string as below. This worked for me

select * from IS_ACC_ACTIVITY_ERROR_LOGS where ERROR_LOG_ID IN( select regexp_substr ((SELECT REPLACE(ERROR_DESCRIPTION,'.','') des FROM IS_ACC_ACTIVITY_ERROR_LOGS WHERE ERROR_LOG_ID = 2), '[^,]+', 1, rn) as splited_string from dual cross join (select rownum as rn from dual connect by level <= 2))