Search code examples
oracle-databaseplsqlsql-like

Escape underscore in PL/SQL

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;

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?

Solution

  • You can use the escape clause:

    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

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