Oracle Database 19c Trigger issue-CodePudding

I am using Oracle Database 19c Enterprise Edition 19 19.0.0.0.0.

I would like to create a trigger that always sets the 1st letter in a column to capitalized.

   create or replace TRIGGER TRIGGER_NAME
   BEFORE INSERT OR UPDATE OF COLUMN_NAME ON TABLE_NAME
   FOR EACH ROW
   BEGIN
   :NEW.COLUMN_NAME := upper(substr(:NEW.COLUMN_NAME,1,1))||substr(:NEW.COLUMN_NAME,2);
   END;

The trigger is created, but I get an ORA-00900: Invalid SQL statement error.

Why and what does the solution look like?

CodePudding user response：

To capitalize the first letter in a string you can use regexp


SELECT
regexp_replace ('string', '[a-z]', upper (substr ('string', 1, 1)), 1, 1, 'i')
FROM dual;

This assumes that the first letter is the one you want to convert. It your input text starts with a number, such as 2 strings then it won't change it to 2 Strings.

CodePudding user response：

Using INITCAP will achieve this:

create or replace TRIGGER TRIGGER_NAME
BEFORE INSERT OR UPDATE OF COLUMN_NAME ON TABLE_NAME
FOR EACH ROW
BEGIN
:NEW.COLUMN_NAME := INITCAP(:NEW.COLUMN_NAME);
END;