Oracle string separated by commas to multiple rows in a table

Question

I have a lookup table as follows:

id descr
1   maths
2   reading
3   science
4   history
5   chemistry
6   physics

I have another table, cases, with the following data

cases
id  name    subjects    
1   ABC     maths,reading
2   DEF     maths, chemistry
3   GHI     physics,chemistry

I have a stored procedure that opens a cursor on the cases table and iterates over each case and inserts the value into the base table and child tables. I have a cases_subjects table which needs entry as follows:

caseId  Subject
1       1   
1       2
2       1
2       5
3       6
3       5

I have a stored procedure like below. It has so many other validations so using SP is the only option. What is the best way to get this done with the below code?

create or replace PROCEDURE INSERT_CASES AS 
v_subj number;
cursor c1 is select * from  cases;
begin
       for t_case in c1
        if t_case.subjects is not null then
            --use INSTR or call function to convert one row from cases to many rows in cases_subjects
        else
            --do nothing
        end if;
       end loop;       
       commit;
exception
when others then 
    log_error(0,  'INSERT_case - exception outside' || SQLERRM || ' code ' || SQLCODE, 1);
    commit;
end;

Any input is highly appreciated.

Does this answer your question? [Splitting string into multiple rows in Oracle](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) — astentx, Jan 25 '22 at 22:23
While others have given a technical solution, we'd be remiss if we didn't point out that having delimited, multiple values in a single column is a serious design flaw. It violates the very first rule of normalized data design, upon which relational database theory, as laid out by Codd, is based. Tables should be designed to Third Normal Form. — EdStevens, Jan 25 '22 at 22:31

MT0 · Answer 1 · 2022-01-25T22:32:45.813

You do not need to split the string and can join the table on delimited sub-string matches using LIKE:

SELECT c.id AS caseid,
       l.id AS subjectid
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')

Which, for the sample data:

CREATE TABLE lookup (id, descr) AS
SELECT 1, 'maths'     FROM DUAL UNION ALL
SELECT 2, 'reading'   FROM DUAL UNION ALL
SELECT 3, 'science'   FROM DUAL UNION ALL
SELECT 4, 'history'   FROM DUAL UNION ALL
SELECT 5, 'chemistry' FROM DUAL UNION ALL
SELECT 6, 'physics'   FROM DUAL;

CREATE TABLE cases (id, name, subjects) AS
SELECT 1, 'ABC', 'maths,reading'     FROM DUAL UNION ALL
SELECT 2, 'DEF', 'maths,chemistry'  FROM DUAL UNION ALL
SELECT 3, 'GHI', 'physics,chemistry' FROM DUAL;

CREATE TABLE case_subjects (caseid NUMBER, subjectid NUMBER);

Outputs:

CASEID SUBJECTID

1 1

1 2

2 1

2 5

3 5

3 6

If you want it as an insert then you do not need a cursor and can use INSERT ... SELECT:

INSERT INTO case_subjects(caseid, subjectid)
SELECT c.id,
       l.id
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')

db<>fiddle here

Oracle string separated by commas to multiple rows in a table

1 Answers1