在PL/SQL中转义下划线 [英] Escape underscore in PL/SQL

问题描述

我有一个与此类似的oracle触发器:

I have an oracle trigger similar to this:

AFTER INSERT OR UPDATE ON TABLE_ABC FOR EACH ROW
BEGIN
  IF (:new.COLUMN_A LIKE '%_H') THEN
    INSERT INTO TABLE_DEF (ID, COLUMN_B) VALUES (SEQ_DEF.NEXTVAL, :new.COLUMN_B);  
  END IF;
END;

现在，我想在like子句中转义下划线，以便此触发器仅处理诸如COLUMN_A中的"ABCD_H"或"1234_H"之类的值，而不处理诸如"1234H"之类的值.

Now I want to escape the underscore in the like clause so that only values such as 'ABCD_H' or '1234_H' in COLUMN_A are processed by this trigger but not values such as '1234H'.

如何为给定的示例实现这一目标?

How can I achieve that for the given example?

推荐答案

您可以使用转义子句:

if :new.column_a LIKE '%\_H' escape '\' then

另一种选择是仅提取最后两个字符:

Another option is to just extract the last two characters:

if substr(:new.column_a, -2) = '_H' then

如果substr()的参数为负，则Oracle将从右端提取指定数量的字符.

If the parameter to substr() is negative, Oracle will extract the specified number of characters from the right end.

这篇关于在PL/SQL中转义下划线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！