13

I have a field in a table that is varchar2, 4000 bytes. There are over 50000 rows. Not all rows have data in this field. Few data fields are over 255 bytes, but some are 4000. To place the table in a new application, I need to shorten the field to 255 bytes.

Is there a SQL statement that will reduce the length to 255? I realize data will be lost, that is part of the cost of the new application. The cut should be arbitrary, just stopping the data at 255 no matter the circumstance.

Eric Wilson
  • 57,719
  • 77
  • 200
  • 270
user761758
  • 565
  • 2
  • 6
  • 18

1 Answers1

33
update b set text2 = substr(text2,1,255);

then alter table to set length of column to 255 :

alter table b MODIFY "TEXT2" varchar2(255 byte);
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 9
    as there are only a few rows, I would only update the records that are too big... add `WHERE LENGTH(text2)>255` – Kevin Burton Dec 07 '11 at 09:16
  • 1
    THEORY QUESTION: I'm curious as to how this would help. at first glance I agree it should. But as I started to think about it I began to wonder. with the where, it would have to do a length first (performance hit) On EVERY record as I doubt there's an index on the length. then it has to the the substring. So it seems to me that it might actually be slower. just using the str, it does the count once for every record and it stops at 255 or end of string every time. whereas with length it has to count everything. then do the substring. Just food for thought. – xQbert Dec 07 '11 at 17:54
  • 2
    it woudl depend if there where any UPDATE triggers on the table also. – ShoeLace Feb 24 '12 at 06:19
  • 4
    actually you preobably want `alter table b MODIFY "TEXT2" varchar2(255 CHAR);` CHAR not BYTE – ShoeLace Feb 24 '12 at 06:20
  • 1
    as to why CHAR vs BYTE would matter... http://stackoverflow.com/questions/81448/difference-between-byte-and-char-in-column-datatypes – xQbert Jun 27 '13 at 16:20
  • 2
    Agreed with Kevin. The time needed to get the length of a string is minimal, but the work needed to update a row is much greater in comparison. – Jeffrey Kemp Jul 25 '13 at 05:46
  • as Do I after running though a test involving 370k records and seeing a slightly less cost than not using the where clause limit. (54521 vs 54579) a cost savings of 58 but I think would vary as to the number of records over/under the 255 limit. – xQbert Jul 25 '13 at 14:38
  • @xQbert And for Number type how to do that, I want To change my filed Type **from Number to Number(3)** Thank you :D – Chlebta Apr 15 '14 at 08:51
  • 1
    @Chlebta that's a different question. Pose it I'm sure you'll get a response or two. – xQbert Apr 15 '14 at 13:10