RE: [Q] shrink varchar2 size and truncate data????

How about sth like:  

update functions set instructions=substr(instructions,0,1000) where lentgh(instructions) > 1000;  

(where clause is not necessary, though it could improve performance by using an index should instructions have one)  

Then alter the table.  

hth  

Guillermo Alan Bort

DBA / DBA Main Team  

EDS, an HP company

ITO Arias 1851

Ciudad Autonoma de Buenos Aires (C1429DXC)

Argentina  

Tel: +54 11 4704-3132

E-mail: guillermo.bort_at_eds.com <mailto:name.name_at_eds.com>  

We deliver on our commitments

so you can deliver on yours.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dba1 mcc Sent: Tuesday, October 21, 2008 3:17 PM
To: oracle-l_at_freelists.org
Subject: [Q] shrink varchar2 size and truncate data????  

we have a table has a field define as "varchar2(2000)", we tried to change size from varchar2(2000) TO VARCHAR2(1000). dUE TO SOME RECORDS DATA LARGE THAN 1000.   I can use following sql statemnet to find which records > 1000 characters, but how can I truncate data larger than 1000 characters?  

select id, length(INSTRUCTIONS) from functions where length(INSTRUCTIONS) >=1000;  

--
http://www.freelists.org/webpage/oracle-l