Search code examples
mysqlsqlddlalter-table

Force Truncation on VARCHAR modification?

I would like to cap VARCHAR to 255 on a few columns so that I can add an index.

alter table striker modify contacts varchar(255)

When I try to run the above command I get

Error Code: 1265. Data truncated for column 'contacts' at row 331   38.969 sec

Is there a way to force truncation of the column so that this command successfully shortens the VARCHAR? I don't care about shortening the data in the column.

Solution

  • You can manually truncate the column at 255 characters:

    UPDATE striker
SET    contacts = SUBSTR (contacts, 1, 255);

    Now, that you know that no value is longer than 255 characters, you perform the alter table statement you have in the OP safely.