SQL REPLACE giving error: 'String or binary data would be truncated.'

Question

I'm trying to replace just part of a string in our company database. The column in which I'm trying to update is MERGECODES (varchar(20),null). A typical value of this column would be something like 'M, GPE, T'.

I would like to replace every instance of T with KD but I'm getting the error below. It will allow me to change anything with the same number of characters or less, for example, it will allow me to replace T with K but not with KD. Any help would be greatly appreciated. Thanks guys!

Code:

UPDATE GoldMine.dbo.CONTACT1 
SET MERGECODES = REPLACE(MERGECODES, 'T', 'KD')

ERROR:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated. The statement has been terminated.

Answer 1

You need to increase your data type size.

Currently you have varchar(20).

If the data is 20 characters long, and you replace 1 character for 2, then that will be 21 characters long, which will cause truncation.

Try increasing your data type to varchar(50) for example, and this should resolve your problem.