时间戳

首页 > 解决方案 > 在 oracle 中使用 REGEXPR_SUBSTR 的模式匹配条件

oracle - 在 oracle 中使用 REGEXPR_SUBSTR 的模式匹配条件

问题描述

我有以下 clob 列，我需要使用 oracle 从下面的列中提取 RuleName。

Clob 列：

"声明 ABBaseTransKey AB_Base_Trans.Key%type := :P_KEY;hRowID RowID;TestMode varchar2(1) := :TESTING;Dummy varchar2(1);RuleName varchar2(1000);IncreaseRunLevel boolean := false;Begin If TestMode = 'N ' 然后从 AB_Base_Trans 将 RowID 选择到 hRowID 中，其中 Key = ABBaseTransKey;end if; RuleName := 'HKG-MRG-UA-PRODUCT-CATEGORY/UA/UA01';update ab_base_trans set CC_PRODUCT_CATEGORY = 'CROSS DOCK' where Key = ABBaseTransKey and RowID = hRowID 和 Client_ID = 'UA' and Site_ID = 'UA01' and TestMode = 'N' and (DOC_TYPE = 'A' AND ((LOTTABLE_07 = 'TRANSLOAD') OR (LOTTABLE_07 = 'UARESERVED'))); RuleName:= 'HKG-MRG-UA-PRODUCT-CATEGORY-PIKPAK/UA/UA01';更新 ab_base_trans 设置 CC_PRODUCT_CATEGORY = 'PICK PACK' 其中 Key = ABBaseTransKey and RowID = hRowID and Client_ID = 'UA' and Site_ID = 'UA01' and TestMode = 'N' and (DOC_TYPE = 'A' AND ((LOTTABLE_07 <> 'TRANSLOAD') AND (LOTTABLE_07 <> 'UARESERVED'))); RuleName := 'HKG-MRG-UA-RTN-RCPT/UA/UA01';更新 ab_base_trans 设置 CC_BILLABLE_QUANTITY = ABS(BILLABLE_QUANTITY) 其中 Key = ABBaseTransKey and RowID = hRowID and Client_ID = 'UA' and Site_ID = 'UA01' and TestMode = 'N' 和（Doc_Type = 'J' 和 Doc_Sub_Type = 'RET'）； 规则名称:= 'HKG-MRG-UA-SPECIAL-RATE-CODE/UA/UA01';更新 ab_base_trans 设置 CC_Special_Rate_Code = 'SMS' 其中 Key = ABBaseTransKey and RowID = hRowID and Client_ID = 'UA' and Site_ID = 'UA01' and TestMode = 'N' 和 (((DOC_TYPE = 'A') OR (DOC_TYPE = 'N') OR (DOC_TYPE = 'P')) AND ((SUBSTR(LOTTABLE_01,1,1) = 'S') OR(SUBSTR (LOTTABLE_01,1,1) = 'M')) AND (END_DSTAMP BETWEEN (SELECT USER_DEF_DATE_1 FROM CLIENT_ID='UA') AND (SELECT USER_DEF_DATE_2 FROM CLIENT WHERE CLIENT_ID='UA'))); 规则名称:= 'HKG-MRG-UA-SPECIAL-RATE-CODE-NA/UA/UA01';更新 ab_base_trans 设置 CC_Special_Rate_Code = 'NA' 其中 Key = ABBaseTransKey and RowID = hRowID and Client_ID = 'UA' and Site_ID = 'UA01'和 TestMode = 'N' 和 (NOT( ((DOC_TYPE = 'A') OR (DOC_TYPE = 'N') OR (DOC_TYPE = 'P')) AND ((SUBSTR(LOTTABLE_01,1,1) = 'S' ) OR(SUBSTR(LOTTABLE_01,1,1) = 'M')) AND (END_DSTAMP BETWEEN (SELECT USER_DEF_DATE_1 FROM CLIENT_ID='UA') AND (SELECT USER_DEF_DATE_2 FROM CLIENT_ID='UA')) )); 规则名称:= 'HKG-MRG-UA-VAS-LABEL-COUNT-BASE/UA/UA01';更新 ab_base_trans 设置 CC_LABEL_COUNT = Billable_Carton * R_LABEL_COUNT 其中 Key = ABBaseTransKey and RowID = hRowID and Client_ID = 'UA' and Site_ID = 'UA01'和 TestMode = 'N' 和 (DOC_TYPE = 'LABEL'); <> LibABBaseTransMergeRule.SetIncreaseRunLevel(IncreaseRunLevel); 空值; 其他人则例外 LibABBaseTransMergeRule.SetErrorCode (ABBaseTransKey, RuleName, 'UA', 'UA01', sqlerrm); LibABBaseTransMergeRule.SetIncreaseRunLevel (false); 增加; 结尾;”

标签： oracleplsqloracle10g

解决方案

这不是一个高科技选项，但是 - 看看它是否有帮助。

  • t1CTE 返回包含“RuleName :=" 字符串的所有部分，长度为 100 个字符（是否足够长？）
  • finalSELECT然后返回以斜杠结尾的子字符串

可能会有进一步的调整（是否总是“RuleName”（字母大小写），是否总是在该名称和“=”之间只有一个空格等）但是 - 作为第一步可能没问题。

SQL> with t1 as
  2    (select substr(col, instr(col, 'RuleName :=', 1, level) + 13, 100) res1
  3     from a
  4     connect by level <= regexp_count(col, 'RuleName :=')
  5    )
  6  select substr(res1, 1, instr(res1, '/') - 1) result
  7  from t1;

RESULT
--------------------------------------------------------------------------------
HKG-MRG-UA-PRODUCT-CATEGORY
HKG-MRG-UA-PRODUCT-CATEGORY-PIKPAK
HKG-MRG-UA-RTN-RCPT
HKG-MRG-UA-SPECIAL-RATE-CODE
HKG-MRG-UA-SPECIAL-RATE-CODE-NA
HKG-MRG-UA-VAS-LABEL-COUNT-BASE

6 rows selected.

SQL>

但是，如果您只想知道该列中是否存在某个 RuleNameINSTR就足够了：

SQL> select case when instr(col, 'HKG-MRG-UA-RTN-RCPT') > 0 then 'It exists'
  2              else 'It does not exist'
  3         end result
  4  from a;

RESULT
-----------------
It exists

SQL>

推荐阅读