Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE students (id, room_number) AS
SELECT 1, '1234' FROM DUAL UNION ALL
SELECT 2, '5678' FROM DUAL UNION ALL
SELECT 3, '1234,5678' FROM DUAL UNION ALL
SELECT 4, '9999' FROM DUAL;
4 rows affected

CREATE TABLE other_table (room_list) AS
SELECT '1234,5678' FROM DUAL;
1 rows affected

SELECT s.*
FROM   students s
       INNER JOIN other_table o
       ON (',' || o.room_list || ',' LIKE '%,' || s.room_number || ',%');


ID
ROOM_NUMBER


1
1234

2
5678

3
1234,5678