Oracle 11g Release 2

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

with data
 as (
   select 12345 as client_id,';'||'6882|HOME;8273|WORK;3192|OTHER' as address_agg_typ from dual union all
   select 52345 as client_id,';'||'5523|OTHER;1345|HOME;9547|WORK' as address_agg_typ from dual union all
   select 74563 as client_id,';'||'4431|OTHER;6456|WORK;7567|HOME' as address_agg_typ from dual union all
   select 34534 as client_id,';'||'1543|WORK;5634|HOME;5123|OTHER' as address_agg_typ from dual 
    )
select client_id
      ,substr(
              regexp_substr(address_agg_typ,';[0-9]*\|HOME')
              ,2
              ,instr(regexp_substr(address_agg_typ,';[0-9]*\|HOME'),'HOME')
               -3
              ) as home_extract
      ,substr(
              regexp_substr(address_agg_typ,';[0-9]*\|WORK')
              ,2
              ,instr(regexp_substr(address_agg_typ,';[0-9]*\|WORK'),'WORK')
               -3
              ) as work_extract              
      ,substr(
              regexp_substr(address_agg_typ,';[0-9]*\|OTHER')
              ,2
              ,instr(regexp_substr(address_agg_typ,';[0-9]*\|OTHER'),'OTHER')
               -3
              ) as other_extract                            
  from data

CLIENT_ID	HOME_EXTRACT	WORK_EXTRACT	OTHER_EXTRACT
12345	6882	8273	3192
52345	1345	9547	5523
74563	7567	6456	4431
34534	5634	1543	5123