Force Truncation on VARCHAR modification?

Question

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.

Answer 1

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.