Home » SQL & PL/SQL » SQL & PL/SQL » UPPER CASE update
UPPER CASE update [message #282204] Wed, 21 November 2007 03:03 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
When i do the following update i still get uppercase when i do a select
SQL> update scott.emp set ename=LOWER(ENAME);

16 rows updated.

SQL> desc scott.emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select ename from scott.emp ;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.

How do you store update your table to have all values converted and stored in lower case on if you need them to be in upper case how do you do the update

i used example

update scott.emp set ename = UPPER(ENAME) - to have all ename update to upper case

is this update write if i run on a table with 1 million rows to change all values to upper case for a column
Re: UPPER CASE update [message #282218 is a reply to message #282204] Wed, 21 November 2007 03:40 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Did you commit after update?

Commit the session then try select * from emp;

Regards,
Kiran

[Updated on: Wed, 21 November 2007 03:41]

Report message to a moderator

Re: UPPER CASE update [message #282221 is a reply to message #282218] Wed, 21 November 2007 03:44 Go to previous messageGo to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
commit;

still shows upper. I think oracle returns the results as upper by default.
Re: UPPER CASE update [message #282222 is a reply to message #282204] Wed, 21 November 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> update scott.emp set ename=LOWER(ENAME);

14 rows updated.

SQL>  select ename from scott.emp ;
ENAME
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller

14 rows selected.

You surely did something wrong like selecting in another session without committing in the first one.

Regards
Michel

Re: UPPER CASE update [message #282229 is a reply to message #282204] Wed, 21 November 2007 03:54 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Oracle will not show in uppercase by default.

[Updated on: Wed, 21 November 2007 03:55]

Report message to a moderator

Re: UPPER CASE update [message #282234 is a reply to message #282229] Wed, 21 November 2007 04:01 Go to previous messageGo to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
ok, worked fine.
Re: UPPER CASE update [message #282311 is a reply to message #282229] Wed, 21 November 2007 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
kir_ait wrote on Wed, 21 November 2007 04:54

Oracle will not show in uppercase by default.


Huh?
Re: UPPER CASE update [message #282316 is a reply to message #282229] Wed, 21 November 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kir_ait wrote on Wed, 21 November 2007 10:54

Oracle will not show in uppercase by default.

SQL> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Oracle shows the data as they are in the database. It does not care about lower or upper case (which in meaningless in many languages).

Regards
Michel

[Updated on: Wed, 21 November 2007 08:27]

Report message to a moderator

Re: UPPER CASE update [message #282317 is a reply to message #282311] Wed, 21 November 2007 08:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Wed, 21 November 2007 15:11

kir_ait wrote on Wed, 21 November 2007 04:54

Oracle will not show in uppercase by default.


Huh?

This is in response to the original poster when he claimed he committed:
Quote:

still shows upper. I think oracle returns the results as upper by default.
Re: UPPER CASE update [message #282318 is a reply to message #282317] Wed, 21 November 2007 08:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Frank. Now it all makes sense. Well, maybe.
Re: UPPER CASE update [message #282320 is a reply to message #282317] Wed, 21 November 2007 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah OK! I didn't understand either.

Regards
Michel

Re: UPPER CASE update [message #282814 is a reply to message #282204] Fri, 23 November 2007 14:55 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Run the following code. It looks like you might have a trigger on the table that forces ename to uppercase.

select trigger_name 
from all_triggers
where table_owner = 'SCOTT'
and table_name = 'EMP';
Previous Topic: strange behaviour when updating
Next Topic: How to move data between two partitions?
Goto Forum:
  


Current Time: Mon Dec 05 07:05:23 CST 2016

Total time taken to generate the page: 0.08725 seconds