Oracle 18c

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

CREATE TABLE TEST
(name varchar2(255),
code varchar2(255)
);
begin
insert into test values ('ABC-Amherst','1179;348;403;407;408;412;413;426;428;430');
insert into test values ('ABC-Amherst','1179;352;401;403;412;413;416;420;433;A40');
insert into test values ('ABC-Ville','1179;352;401;403;412;413;416;420;433;A40');
end;
/
1 rows affected

select distinct t.name, x.code
from test t
cross apply json_table(
    '["' || replace(t.code, ';', '","') || '"]',
    '$[*]' columns (code varchar(50) path '$')
) x
order by t.name, x.code


NAME
CODE


ABC-Amherst
1179

ABC-Amherst
348

ABC-Amherst
352

ABC-Amherst
401

ABC-Amherst
403

ABC-Amherst
407

ABC-Amherst
408

ABC-Amherst
412

ABC-Amherst
413

ABC-Amherst
416

ABC-Amherst
420

ABC-Amherst
426

ABC-Amherst
428

ABC-Amherst
430

ABC-Amherst
433

ABC-Amherst
A40

ABC-Ville
1179

ABC-Ville
352

ABC-Ville
401

ABC-Ville
403

ABC-Ville
412

ABC-Ville
413

ABC-Ville
416

ABC-Ville
420

ABC-Ville
433

ABC-Ville
A40


select t.name, x.code
from test t
cross apply (
	select distinct code
	from json_table(
		'["' || replace(t.code, ';', '","') || '"]',
		'$[*]' columns (code varchar(50) path '$')
	) x
) x
order by t.name, x.code


NAME
CODE


ABC-Amherst
1179

ABC-Amherst
1179

ABC-Amherst
348

ABC-Amherst
352

ABC-Amherst
401

ABC-Amherst
403

ABC-Amherst
403

ABC-Amherst
407

ABC-Amherst
408

ABC-Amherst
412

ABC-Amherst
412

ABC-Amherst
413

ABC-Amherst
413

ABC-Amherst
416

ABC-Amherst
420

ABC-Amherst
426

ABC-Amherst
428

ABC-Amherst
430

ABC-Amherst
433

ABC-Amherst
A40

ABC-Ville
1179

ABC-Ville
352

ABC-Ville
401

ABC-Ville
403

ABC-Ville
412

ABC-Ville
413

ABC-Ville
416

ABC-Ville
420

ABC-Ville
433

ABC-Ville
A40

NAME	CODE
ABC-Amherst	1179
ABC-Amherst	348
ABC-Amherst	352
ABC-Amherst	401
ABC-Amherst	403
ABC-Amherst	407
ABC-Amherst	408
ABC-Amherst	412
ABC-Amherst	413
ABC-Amherst	416
ABC-Amherst	420
ABC-Amherst	426
ABC-Amherst	428
ABC-Amherst	430
ABC-Amherst	433
ABC-Amherst	A40
ABC-Ville	1179
ABC-Ville	352
ABC-Ville	401
ABC-Ville	403
ABC-Ville	412
ABC-Ville	413
ABC-Ville	416
ABC-Ville	420
ABC-Ville	433
ABC-Ville	A40