Escape underscore in PL/SQL

Question

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?

Answer 1

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.

Answer 2

IF (:new.COLUMN_A LIKE '%_H')

You could use the ESCAPE keyword.

For example.

SQL> SET serveroutput ON
SQL> DECLARE
  2    str VARCHAR2(10) := '_ ';
  3  BEGIN
  4    IF str LIKE '%\_ %' THEN
  5      dbms_output.put_line('did not escape');
  6    ELSIF str LIKE '%\_ %' ESCAPE '\' THEN
  7      dbms_output.put_line('escaped');
  8    END IF;
  9  END;
 10  /
escaped

PL/SQL procedure successfully completed.

In SQL*Plus it is even easier, you could do:

SET ESCAPE '\'

For example,

SQL> SET ESCAPE '\'
SQL> SET serveroutput ON
SQL> DECLARE
  2    str VARCHAR2(10) := '_ ';
  3  BEGIN
  4    IF str LIKE '%\_ %' THEN
  5      dbms_output.put_line('did not escape');
  6    ELSIF str LIKE '%\_ %' ESCAPE '\' THEN
  7      dbms_output.put_line('escaped');
  8    END IF;
  9  END;
 10  /
did not escape

PL/SQL procedure successfully completed.

See, in SQL*Plus it did not go to the ELSIF part, as it could escape it in the IF condition itself with SET ESCAPE '\'