Changing a varchar2 field to uppercase

Published: 17 May 2002

Please advise on changing data in a varchar2 column to all uppercase. It currently holds a mix of upper- and l...

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please log in.

You have exceeded the maximum character limit.

Please provide a Corporate Email Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

owercase.

You can make the change to all current rows with a single update statement as follows:

UPDATE my_table
   SET my_column = UPPER(my_column) ;

CREATE TRIGGER make_upper
BEFORE UPDATE OR INSERT ON my_table
FOR EACH ROW
begin
  :new.my_column := upper(:new.my_column) ;
end ;
/

What do you think about this answer? E-mail the editors at [email protected] with your feedback.
The Best Oracle Web Links: tips, tutorials, scripts, and more.
Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

You could then also "force" all future data entered into that column to be stored in upper case by creating a simple trigger as follows: