一个选项是使用instr函数：
样本数据：

SQL> with test (col) as
  2    (select 'aaa-xyz-bbb' from dual union all
  3     select 'xyz-mmm-ooo' from dual union all
  4     select 'aaa-ttt-eee' from dual
  5    )

质询：

6  select col,
  7    case when instr('-' || col || '-', '-xyz-') > 0 then 'xyz'
  8         else 'Null'
  9    end result
 10  from test;

COL         RESULT
----------- ----------
aaa-xyz-bbb xyz
xyz-mmm-ooo xyz
aaa-ttt-eee Null

SQL>