Oracle SQL Join an ID on a field with a list of IDs some with ranges

First of all, its not my database and I cannot change it:)

This is an oracle database

I have a table with 2 ID fields code and subcode:

Table1
ID Code Subcode
1  300  010
2  400  050
3  300  060

I have another table with a code range field that has a comma separated list of codes however some have a range.

Table2
ID CodeRange 
1  300.020,300.040-300.060
2  300.000-300.020,400.010
3  300.180,400.010-400.100,500.010

I wish to do a join that would pull out:

Table1.ID Table2.ID
1         2
2         3
3         1

To note: The first number can change in the range IE 300.010-400.190

The direction I am heading is to combine the Code and subcode into one number like:

TO_NUMBER(CONCAT(CONCAT(Code, '.'),Subcode))

Then look at splitting the string in substrings(numbers) and comparing or using find_in_set or something of that nature but I cannot get there. The issue is The CodeRange has as many as 20 different ranges but each one varies in size.

Any help would be appreciated. Thanks

Answer

I had a similar approach to @Barbaros. By splitting the CSVs in Table2 into their own rows, you can then split the ranges into a beginning/end range and the explicit values as the same value for beginning/end, then do a numeric comparison.

WITH
    table1 (ID, Code, Subcode)
    AS
        (SELECT 1, '300', '010' FROM DUAL
         UNION ALL
         SELECT 2, '400', '050' FROM DUAL
         UNION ALL
         SELECT 3, '300', '060' FROM DUAL),
    table2 (ID, CodeRange)
    AS
        (SELECT 1, '300.020,300.040-300.060' FROM DUAL
         UNION ALL
         SELECT 2, '300.000-300.020,400.010' FROM DUAL
         UNION ALL
         SELECT 3, '300.180,400.010-400.100,500.010' FROM DUAL)
  SELECT t1.id AS t1_id, t2.id AS t2_id
    FROM table1 t1
         JOIN
         (SELECT id,
                 REGEXP_SUBSTR (coderange,
                                '[^,]+',
                                1,
                                commas.COLUMN_VALUE)    AS codes
            FROM table2,
                 TABLE (
                     CAST (
                         MULTISET (
                                 SELECT LEVEL
                                   FROM DUAL
                             CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (coderange, '[^,]+')) + 1)
                             AS SYS.OdciNumberList)) commas) t2
             ON (TO_NUMBER (t1.code || '.' || t1.subcode) BETWEEN TO_NUMBER (SUBSTR (t2.codes, 1, 7))
                                                              AND TO_NUMBER (
                                                                      NVL (SUBSTR (t2.codes, 9),
                                                                           SUBSTR (t2.codes, 1, 7))))
ORDER BY t1.id;