3

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.

Improve this question
2
  • 1
    If you really have M, GPE, T in a varchar(20) column, and you execute your REPLACE - it just works without any problem. I'm thinking that you're probably updating too many rows and at least one of the rows your UPDATE statement hits contains too many characters in that column for the REPLACE to work. Try to use a WHERE id = 42 or something clause - in order to update just a single row which you know is OK. Commented Aug 14, 2012 at 15:05
  • Why did someone down vote this question? Please let me know if I did something wrong here. Commented Aug 14, 2012 at 15:34

1 Answer 1

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.