regexp_like的有关问题_Oracle开发

regexp_like的问题
表一：
anz_asset_product
oid asset_product_code
01 CA000
02 EQU111
03 UTX222

表二：
product_subtype
oid product_subtype_code
11 CA
12 EQU

题目是找出anz_asset_product中下面的记录
03 UTX222

因为UTX不存在于product_subtype表中，该如何实现呢，我知道有
SELECT *
FROM anz_asset_product anz
WHERE NOT EXISTS
(SELECT 1
FROM product_subtype p
WHERE p.product_subtype_code =
substr(anz.asset_product_code, 1, length(regexp_replace(anz.asset_product_code, '([[:alpha:]]*)([[:digit:]]*)', '\1'))));

可实现，可有没有更简单的方法，用regexp_like自动去匹配呢？

------解决方案--------------------
instr 函数

SQL code

with anz_asset_product as(select  '01' oid, 'CA000' asset_product_code from dual union all select '02', 'EQU111' from dual union allselect '03', 'UTX222' from dual),product_subtype as(select '11' oid, 'CA' product_subtype_code from dual union allselect '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 codewith anz_asset_product as(select '01' oid,'CA000' asset_product_code from dualunion allselect '02','EQU111' from dualunion allselect '03','UTX222' from dual),product_subtype as(select '11' oid,'CA' product_subtype_code from dualunion allselect '12','EQU' from dual)SELECT *FROM anz_asset_product anzWHERE 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 codeselect a.asset_product_code from anz_asset_product aminusselect a.asset_product_code from anz_asset_product a , product_subtype bwhere regexp_like(a.asset_product_code, b.product_subtype_code, 'i');