How to return values between two different delimiters using SQL?

select column1
    ,split(column1,'|') as s
    ,array_size(s) as s_len
    ,floor((s_len+1)/2) as idx
    ,s[idx-1] as middle_item
    ,split(middle_item,'-') as sb
    ,array_size(sb) as sb_len
    ,floor((sb_len+1)/2) as b_idx
    ,sb[b_idx-1] as b_middle_item
from values
('6891-HCB-ENG|6433C-MMW-ENG|6433C-MMC-ENG|5675-HBC-ENG'),
('6891-HCB-ENG|6433C-MMW-ENG|6433C-MMC-ENG');

gives:

which can be compacted to:

select column1
    ,split(column1,'|') as s
    ,s[floor((array_size(s)+1)/2)-1] as middle_item
    ,split(middle_item,'-') as sb
    ,sb[floor((array_size(sb)+1)/2)-1] as b_middle_item
from values
('6891-HCB-ENG|6433C-MMW-ENG|6433C-MMC-ENG|5675-HBC-ENG'),
('6891-HCB-ENG|6433C-MMW-ENG|6433C-MMC-ENG');

Taking all middle parts:

select 
    array_agg(split_part(s.value,'-',2)) within group(order by s.index) as middle_bits
from values
('6891-one-ENG|6433C-two-ENG|6433C-three-ENG|5675-four-ENG'),
('6891-one-ENG|6433C-two-ENG|6433C-three-ENG'),
('6891-one-ENG|6433C-two-ENG'),
('6891-one-ENG')
as t
,table(split_to_table(t.column1,'|')) as s
group by s.seq
order by s.seq
;

using listagg and it becomes are string:

select 
    listagg(split_part(s.value,'-',2), ', ') within group(order by s.index) as middle_bits
from values
('6891-one-ENG|6433C-two-ENG|6433C-three-ENG|5675-four-ENG'),
('6891-one-ENG|6433C-two-ENG|6433C-three-ENG'),
('6891-one-ENG|6433C-two-ENG'),
('6891-one-ENG')
as t
,table(split_to_table(t.column1,'|')) as s
group by s.seq
order by s.seq
;

We could try a regex replacement approach:

SELECT val, REGEXP_REPLACE(val, '^.*?-|-.*$', '') AS middle
FROM yourTable;

Here is a regex demo showing that the replacement logic is working.