with anz_asset_product as
(select  '01' oid, 'CA000' asset_product_code from dual union all 
select '02', 'EQU111' from dual union all
select '03', 'UTX222' from dual),
product_subtype as
(select '11' oid, 'CA' product_subtype_code from dual union all
select '12' , 'EQU' from dual)
select * from anz_asset_product t1 
where not exists 
(select 1 from product_subtype t2 where instr(t1.asset_product_code,t2.product_subtype_code,1,1)<>0)

------解决方案--------------------
SQL code

with anz_asset_product as(
select '01' oid,'CA000' asset_product_code from dual
union all
select '02','EQU111' from dual
union all
select '03','UTX222' from dual
),
product_subtype as(
select '11' oid,'CA' product_subtype_code from dual
union all
select '12','EQU' from dual
)SELECT *
FROM anz_asset_product anz
WHERE NOT EXISTS
 (SELECT 1
  FROM product_subtype p
  WHERE anz.asset_product_code like p.product_subtype_code||'%' );   --根据你的要求，用like就可以了
 
OID ASSET_PRODUCT_CODE
--- ------------------
03  UTX222

------解决方案--------------------
这样呢：
SQL code
select a.asset_product_code from anz_asset_product a
minus
select a.asset_product_code from anz_asset_product a , product_subtype b
where regexp_like(a.asset_product_code, b.product_subtype_code, 'i');