In Oracle SQL, how to join tables with multi-valued column

oracle, sql

Solution

Yuch. But sometimes you have to deal with this. In Oracle, you can use `like` for the `join` condition:

select s.id, s.name, d.date
from schedule s join
     dates d
     on ',' || dates || ',' like '%,' || d.id || ',%';

This is not efficient and it won't make use of indexes. But it should solve your problem.

Note the use of the delimiter `','`. This prevents `10` from matching `100`.

Problem

We have a legacy table in our Oracle database which has a column that takes comma-separated values. These comma-separated values are actually foreign keys to another table. ``` Table: SCHEDULE ----------------------- ID NAME DATES -- ---- ----- 1 Test1 10,20,30 2 Test2 20,40 Table: DATES ----------------------- ID DATE -- ---- 10 2013-01-01 20 2013-02-02 30 2013-03-03 40 2013-04-04 ``` I'm trying to write a query that would return something like below result: ``` ID NAME DATE -- ---- ---- 1 Test1 2013-01-01 1 Test1 2013-02-02 1 Test1 2013-03-03 2 Test2 2013-02-02 2 Test2 2013-04-04 ``` I came across DBMS_UTILITY.comma_to_table procedure, and functions like REGEXP_SUBSTR, SPLIT, JOIN, etc. But I'm not able to achieve this. Any help here?

Original source