Truncate SQL varchar column

Question

I currently have a column of varchar(3000). I want to reduce that to 400 characters.

When I execute this query:

ALTER TABLE [dbo].[App]
ALTER COLUMN SpecialInstructions VARCHAR(400) NOT NULL

I get this error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

How do I set where previous data in that column would just be cut off? I'm using a development database, so it wouldn't matter if I was losing information. Or is there a better way to do this? Thanks in advance.

Answer 1

update [dbo].[App]
set  SpecialInstructions = left(SpecialInstructions, 400)

You can optionally add a WHERE clause to make sure only too long values are shortened:

where len(SpecialInstructions) > 400

This is to reduce the number of rows updated. Keeps transaction smaller, incl write-sets.