How to Split a String by Delimiters and Handle Empty Values as NULL in Oracle PL/SQL?

I have a string in Oracle PL/SQL that looks like this:

280,1,2,3,3 | 120,,0,2,3 | 280,1,2,3,3

Each section is separated by a | character, and within each section, values are separated by commas. The challenge I’m facing is correctly extracting the values, and for missing or empty values

For example, the data I expect is:

• Country ID: 280, EIN: 1, SSN: 2, ITIN: 3, ATIN: 3
• Country ID: 120, EIN: NULL, SSN: 0, ITIN: 2, ATIN: 3
• Country ID: 280, EIN: 1, SSN: 2, ITIN: 3, ATIN: 3

Here’s what I have tried:

DECLARE
    v_entry VARCHAR2(500); 
    v_countries VARCHAR2(500) := '280,1,2,3,3 | 120,,0,2,3 | 280,1,2,3,3';
    v_country_id VARCHAR2(50);
    v_ein VARCHAR2(50);
    v_ssn VARCHAR2(50);
    v_itin VARCHAR2(50);
    v_atin VARCHAR2(50);
BEGIN
    FOR i IN 1 .. REGEXP_COUNT(v_countries, '\|') + 1 LOOP
        v_entry := REGEXP_SUBSTR(v_countries, '[^|]+', 1, i);
    
        v_country_id := REGEXP_SUBSTR(v_entry, '([^,]*)', 1, 1);
        v_ein := REGEXP_SUBSTR(v_entry, '([^,]*)', 1, 2);
        v_ssn := REGEXP_SUBSTR(v_entry, '([^,]*)', 1, 3);
        v_itin := REGEXP_SUBSTR(v_entry, '([^,]*)', 1, 4);
        v_atin := REGEXP_SUBSTR(v_entry, '([^,]*)', 1, 5);

        v_country_id := CASE WHEN v_country_id IS NULL OR v_country_id = '' THEN 'NULL' ELSE v_country_id END;
        v_ein := CASE WHEN v_ein IS NULL OR v_ein = '' THEN 'NULL' ELSE v_ein END;
        v_ssn := CASE WHEN v_ssn IS NULL OR v_ssn = '' THEN 'NULL' ELSE v_ssn END;
        v_itin := CASE WHEN v_itin IS NULL OR v_itin = '' THEN 'NULL' ELSE v_itin END;
        v_atin := CASE WHEN v_atin IS NULL OR v_atin = '' THEN 'NULL' ELSE v_atin END;

        DBMS_OUTPUT.PUT_LINE('Country ID: ' || v_country_id);
        DBMS_OUTPUT.PUT_LINE('EIN: ' || v_ein);
        DBMS_OUTPUT.PUT_LINE('SSN: ' || v_ssn);
        DBMS_OUTPUT.PUT_LINE('ITIN: ' || v_itin);
        DBMS_OUTPUT.PUT_LINE('ATIN: ' || v_atin);
    END LOOP;
END;

The issue:
I’m expecting to handle empty fields between commas as NULL, but the output doesn’t reflect this correctly.

For example, for the second section 120,,0,2,3, I expect:

• Country ID: 120
• EIN: NULL
• SSN: 0
• ITIN: 2
• ATIN: 3

But instead, I get unexpected behavior where either values aren’t correctly assigned or the NULL values don’t show up properly.

You need to sign in to view this answers