Oracle 11g Release 2

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

create table mytable ( name varchar(20), address varchar(100));
insert into mytable values('CompanyA', '119021, foo, a 16; 117312, barn a 19');
1 rows affected

SELECT t.*, regexp_count(t.address, ';') + 1 FROM mytable t;

NAME
ADDRESS
REGEXP_COUNT(T.ADDRESS,';')+1

CompanyA
119021, foo, a 16; 117312, barn a 19
2

SELECT TRIM(regexp_substr( t.address, '[^;]+', 1,  LEVEL )) regexp_substr
FROM mytable t
CONNECT BY LEVEL <= regexp_count( t.address,  ';' ) + 1;

REGEXP_SUBSTR

119021, foo, a 16

117312, barn a 19

CREATE TABLE newtable AS
    SELECT t.name, TRIM(regexp_substr( t.address, '[^;]+', 1,  LEVEL )) regexp_substr
    FROM mytable t
    CONNECT BY LEVEL <= regexp_count( t.address,  ';' ) + 1;
2 rows affected

select * from newtable;

NAME
REGEXP_SUBSTR

CompanyA
119021, foo, a 16

CompanyA
117312, barn a 19