String or Binary Data Would be Truncated Error – When Trying to Alter a Column

String or binary data would be truncated error glared back at me when I tried to alter a column from a VARCHAR(100) to a VARCHAR(50).

The reason was obvious, there was data in the table that was longer that what I was trying to make the new column size.

When you encounter an issue like this you have to ask yourself whether or not that matters. Is it better to not change the column size and keep it the way it is, or does it not matter and you can simply truncate the data?

How to Alter a Column and Allow Truncation

If you decide that it is ok to truncate the data you can simply turn off ANSI_WARNINGS e.g:

SET ANSI_WARNINGS OFF
GO

Alternatively you could identify and trim the text yourself by using the following SQL statements.

In this case I will identify any rows > 50 characters, and trim them back a bit.

USE DBAFire_DB

CREATE TABLE #truncator (words VARCHAR(100))

INSERT INTO #truncator
SELECT 'asdkfj;aksjdf;askdfj;asdkfj;asdfjka;sdfljadfasdfaasdfasdfasdfasd'
INSERT INTO #truncator
SELECT 'asdkfj;asdfjka;sdfljadfasdfaasdfasdfasdfasd'

SELECT words FROM #truncator WHERE LEN(words) > 50

-- if you try to alter now you will get the truncation error

ALTER TABLE #truncator ALTER COLUMN words VARCHAR(50)

-- Update the word lenght to no greater than 50 characters

UPDATE #truncator
SET words = LEFT(WORDS, 50) 
WHERE LEN(words) > 50

-- Now you can alter the column

ALTER TABLE #truncator ALTER COLUMN words VARCHAR(50)

-- Clean up
DROP TABLE #truncator

Now you have two methods that you can use to work around this error.

If you do decide to go ahead and truncate, remember to take a backup first as you don’t want to go removing something that may have been required.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply